-
I WANT TO KNOW IF I HAVE RECORD IN MY TABLE BY DOING:
SELECT 'X'
FROM TABLE;
BUT THAT SELECT STATEMENT DOES A LOT OF PHISYCAL READ.
IS THERE A WAY TO FIND OUT IF THERES RECORD IN MY TABLE WITHOUT DOING A LOT OF PHYSICAL READ?
I ALSO DID :
SELECT COUNT(*)
FROM TABLE;
AND IT DOES LESS PHISICAL READ.
BUT I WANT BETTER PERFORMANCE.:)
-
SELECT 'X'
FROM TABLE
where rownum = 1;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
IT DOES A FULL EVEN WITH ROWNUM = 1
-
No, it most certanly does not perform a full scan!
Here is a cut-paste from my SQL*Plus session:
********************************
SQL> select count(*) from ix_table1;
COUNT(*)
---------
22465
SQL> set autotrace traceonly
SQL> select * from ix_table1;
22465 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'IX_TABLE1'
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1564 consistent gets
62 physical reads
0 redo size
874504 bytes sent via SQL*Net to client
166826 bytes received via SQL*Net from client
3002 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
22465 rows processed
SQL> select * from ix_table1 where rownum=1;
1 row selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'IX_TABLE1
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
0 physical reads
0 redo size
1194 bytes sent via SQL*Net to client
674 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
********************************
Pay attention to a second operation in explain plan with rownum=1: it is "COUNT (STOPKEY)".
This means the initiated full table scan will exit immediately when the predefined number of rows (ROWNUM=x) will be found. Also pay attention to the statistics of PHYSICAL READS, DB BLOCK GETS, CONSISTENT GETS and ROWS PROCESSED for both queries - it will convince you that full scan is not executed with ROWNUM=1.
HTH,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Hi!
Can you help me with the output of the analysis. Actually I also want to use the Explain Plan stuff. I also got the similar results but can you tell me where to look for the explanation of the result that is returned by the command?
Thanks!!
-
Hi
you can look at Oracle 8/8i Concept manual chapter 20
-
I am also interested in EXPLAIN PLAN.
I have created a table plan_table in my schema.
I run this query say:
EXPLAIN PLAN
for
select * from test
where rownum=1;
And then do the select * from plan_table..
But I do not get the details in the format that you got..
and I also do not see all the details that you got in there.
I have been trying to find the help on this for a long time.
I also tried online help for the concept manual. It doesn't tell this.
Can you help me please ?
Can you send me the script that you ran to get these results.
Thanks, my email id
Sonaliak@hotmail.com
Sonali
-
Check out [url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76992/ch14_str.htm[/url]
or
[url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/state11d.htm#2061798[/url]
[Edited by marist89 on 01-04-2001 at 12:08 PM]
Jeff Hunter
-
(To ShrutiM and sonaliak)
Regarding the explain plans and statistics from my previous post -
If you just want to format the output of explain plan in a more readable format, there is a bunch of various scripts all over the internet, a couple of them even in oracle documentation. However I find it somewhat clumsy to mess with "EXPLAIN_PLAN_and_then_SELECT_FROM_PLAN_TABLE" for each and every sql I want to tune. Nowadays every decent DBA and developement tool or utility incorporates graphical explain plan facility, where you even don't have to actualy run the query.
Long time ago even SQL*Plus entered the arena of those "decent tools" ;-)) and offers an excelent explain plan and query statistics. The basic syntax is
SET AUTOTRACE ON|OFF|TRACEONLY [EXPLAIN] [STATISTICS]
When you set the autotrace on then the result of every query will be followed by an explain plan and/or statistics. There are various combinations possible. I find the following particularlly useful:
SET AUTOTRACE TRACEONLY EXPLAIN
because the query you isue will not actually be run, only explained, which means you can tune long running queries without actually waiting a long time for them to finish.
A word of caution on statistics this facility offer. If you look closely on the statistics in my previous post you'll find it reported a memory sort on both queries. But if you look at the queries it is more than obvious that queries required no sort (no ORDER BY or GROUP BY or merge joins, ....). The sorts were actually performed internaly by explain plan facility when outputing the explain plan! So the statistics it reports does (probably) actually belongs to both the analyzed query and the internal queries on plan table.
If on the other hand you are not familiar with how to read and interpret the explain plan and statistics, then you should find more information in manuals and books. I myself find the Tuning Guide from official Oracle documentation very usefull.
HTH,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thanks a lot jmodic.
You answered all the questions I had.
Which DBA tool would you recommend ?
As you may have guessed I am new to this dba world.
Ours is a startup company and we do not have these tools as yet.
Thanks again
Sonali
Sonali
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|