Home » RDBMS Server » Performance Tuning » Last n records based on date
( ) 1 Vote
| Last n records based on date [message #115716] |
Wed, 13 April 2005 19:16  |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
I was asked a question at an interview the other day about the best way to display in descending order the latest n records, based on a timestamp from a table. I knew about the inline view method, but that requires an order by and a full table scan (I think). The guy then proceeded to show me the following better way.
SQL> create table my_test (col1 number, date1 date);
Table created.
SQL> create index my_index on my_test(date1 desc);
Index created.
SQL> begin
2 for i in 1..500 loop
3 insert into my_test values(dbms_random.value(-100, 100), sysdate + dbms_random.value(-100, 100));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select /*+ INDEX my_test my_index*/ *
2 from my_test
3 where rownum < 10;
And voila, the hint forces the DB to use my_index, retrieves the first ten corresponing records in the table, and is done.
COL1 DATE1
---------- ---------------
-94.429175 20-MAY-05
-92.364396 16-FEB-05
20.7251502 21-JUN-05
48.5259211 07-APR-05
22.3017922 08-FEB-05
40.4748976 27-FEB-05
-75.423417 22-APR-05
-11.616312 16-MAY-05
40.8144797 08-MAR-05
9 rows selected.
But wait, no voila. It didn't work. I watched it work when he did it. Or did I? Should this work? Am I missing something? I've haven't seen anything like this before. Pointing me in the direction of something that discusses the issue in particular would suffice for an answer.
Thanks.
[Updated on: Wed, 13 April 2005 19:17] Report message to a moderator |
|
|
|
| Re: Last n records based on date [message #115722 is a reply to message #115716] |
Wed, 13 April 2005 21:27  |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
A hint is just a hint, not a command, so Oracle may choose to ignore it. If you analyze your table, indexes, and indexed columns, then Oracle will choose the best execution plan, which may or may not use the index. If you are selecting all or most of the rows from the table, then a full table scan is likely to be more efficient than using an index. So, creating an index and providing a hint to use the index, is not a reliable method of ordering the rows. The only reliable method of ordering rows is to use an order by clause. When selecting from a subquery that uses an order by clause within the innser subquery and limits the rows by rownum in the outer subquery, Oralce uses a special method that shows as count (stopkey) in an explained plan and is the fastest method of returning a subset of ordered rows. Whoever did the demonstration was probably selecting just a few rows from a large table and in that instance Oracle happened to use the index and the desired result was obtained.
|
|
|
|
| Re: Last n records based on date [message #115723 is a reply to message #115716] |
Wed, 13 April 2005 21:45 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Tom Kyte has written a lot of stuff on this subject. You can search on his site at:
http://asktom.oracle.com
Here is a link to one of Tom's articles that was published in Oracle Magaine:
http://www.oracle.com/oramag/oracle/01-jul/o41asktom.html
Here is a link to something I wrote on the subject:
http://forums.oracle.com/forums/thread.jsp?forum=75&thread=1645&message=1645&q=706172746974696f6e206f7264657220726f776e75 6d#1645
I don't know if your interviewer was testing you and expecting you to tell him what was wrong with the index method or if he really thought it was an appropriate method. If you plan to appeal the results somehow, then I would quote Tom Kyte and refer them to him. In case you do not know, last I heard, Tom Kyte is a vice president at Oracle Corporation, listed as an Oracle ACE or Oracle's website, has written a lot of Oracle's code, maintains a website where you can post questions that will be answered by him, has written books and articles on Oracle, and is generally recognized as the grand master of all Oracle gurus or an Oracle god.
|
|
|
|
|
|
| Re: Last n records based on date [message #115825 is a reply to message #115716] |
Thu, 14 April 2005 10:41 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Just curious, but wouldn't that query with the index hint have to first scan the entire index, and only then start hitting the table by rowid and stopping after it hits 9 rows?
I need to read those links you referenced Barbara, and maybe run a test. But of course creating and maintaining an index may or may not be all that trivial or desired.
Oh, and did you ask the interviewer why he populated his test table using row-at-a-time, context-switching, slow processing in a plsql loop rather than in a faster single query?
|
|
|
|
|
|
| Re: Last n records based on date [message #115832 is a reply to message #115826] |
Thu, 14 April 2005 11:35 |
pscjhe
Messages: 38
Registered: April 2005
|
Member |
|
|
<quote>
why he populated his test table using row-at-a-time, context-switching, slow processing in a plsql loop rather than in a faster single query?
</quote>
How do you populate 500 rows with dynamic info in one sql statement ?
|
|
|
|
| Re: Last n records based on date [message #115841 is a reply to message #115832] |
Thu, 14 April 2005 12:38 |
pscjhe
Messages: 38
Registered: April 2005
|
Member |
|
|
Let's assume the point of the interviewer is to find a way to use index to find top n rows WITHOUT using order by.
Intuitively b-tree index has date1 sorted and I only need top n of it. Should not index range scan, then ROWID into table be faster than full table scan ? Yes, of course. If it is reverse key index, top n from the beginning of leaf nodes should provide just that. But somehow optimizer doesn't get it. So is it possible ? yes.. Here are my result on 9.2.0.4
tch@TCHTST2> create index my_index on my_test(date1 desc);
Index created.
tch@TCHTST2> select /*+ index(my_test my_index) */ date1 from my_test where date
1 > to_date('01/01/1900','MM/DD/YYYY') and rownum < 10;
DATE1
-----------------
07/23/05 04:42:27
07/23/05 04:21:52
07/22/05 20:57:08
07/22/05 18:51:27
07/22/05 15:48:32
07/22/05 09:48:35
07/22/05 09:40:40
07/21/05 22:49:05
07/21/05 19:08:22
9 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=9 Bytes=63)
1 0 COUNT (STOPKEY)
2 1 INDEX (RANGE SCAN) OF 'MY_INDEX' (NON-UNIQUE) (Cost=2 Card=25 Byte
s=175)
tch@TCHTST2>
Now we look at another feature of index scan, instead of reverse key index, let's use normal index but use different index access path, index range scan descending, the result is same
tch@TCHTST2> drop index my_index;
Index dropped.
tch@TCHTST2> create index my_index on my_test(date1);
Index created.
tch@TCHTST2> select /*+ index_desc(my_test my_index) */ date1 from my_test where
date1 > to_date('01/01/1900','MM/DD/YYYY') and rownum < 10;
DATE1
-----------------
07/23/05 04:42:27
07/23/05 04:21:52
07/22/05 20:57:08
07/22/05 18:51:27
07/22/05 15:48:32
07/22/05 09:48:35
07/22/05 09:40:40
07/21/05 22:49:05
07/21/05 19:08:22
9 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=9 Bytes=63)
1 0 COUNT (STOPKEY)
2 1 INDEX (RANGE SCAN DESCENDING) OF 'MY_INDEX' (NON-UNIQUE) (Cost=26
Card=500 Bytes=3500)
The key here is to provide something in where clause to "force" CBO to use your index.
|
|
|
|
| Re: Last n records based on date [message #115843 is a reply to message #115716] |
Thu, 14 April 2005 12:49 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Ok, I wanted to understand this a little better so I came up with the following test. Please point out any mistakes in assumptions, logic, or implementation, as I'm not 100% sure I do in fact understand yet. Bear with me, but basically:
I created a table with about 45k rows, and ran 3 queries. I then ran the same queries again but on a table with only 5k rows. Each query I did an explain plan and an autotrace statistics. All of this was done on 10.1.0.4 on windows xp.
I did the test twice, once each on the different sized tables, to try to show that if the index scan (range in this case) had to scan the entire index, not just a subset of it, that it would take longer (more consistent gets) on the index/table with 45k rows than the one with 5k rows.
Both sets of queries had the same results (in this regard), which means if I'm understanding correctly that they did in fact only scan the subset of 9 index entries, not the whole index each time. Although I'm not quite clear on where in the plan it shows that (the stopkey for the index scan, I do see where it shows it for the table scan).
As I said each test had 3 queries. The first just did a regular select without a hint, but with the inline view approach to order the results and limit it by the rownum.
The second tried to use the approach described originally in this thread of the index hint with the rownum. But I could not get oracle to follow the index hint, which meant that it did a full table scan instead, which meant it got the wrong results! Although this was extremely fast due to the stopkey optimization, it was too fast because of the wrong results.
The third added an artificial where clause condition on the query that would not affect the logic/results, but would cause oracle to follow the index hint. This turned out to not only get the correct results (as shown in query 1), but also to perform very well, and indicated (as noted above) that the stopkey optimization was done on the index range scan itself.
And below, in blatent defiance of DB and MA, is my code, so that as Tom points out you guys can poke holes in it and validate (or contradict) my assumptions, method, etc:
MYDBA@ORCL >
MYDBA@ORCL > create table test (d date, data char(70));
Table created.
MYDBA@ORCL >
MYDBA@ORCL > insert /*+ append */ into test
2 select sysdate + dbms_random.value(1,10000), 'x'
3 from all_objects where rownum < 45000;
44999 rows created.
MYDBA@ORCL > commit;
Commit complete.
MYDBA@ORCL >
MYDBA@ORCL > create index testidx on test(d desc);
Index created.
MYDBA@ORCL >
MYDBA@ORCL > exec dbms_stats.gather_table_stats(user,'test',cascade=>true);
PL/SQL procedure successfully completed.
MYDBA@ORCL >
MYDBA@ORCL > set timing on;
MYDBA@ORCL >
MYDBA@ORCL > explain plan for
2 select * from ( select * from test order by d desc) where rownum < 10;
Explained.
Elapsed: 00:00:00.09
MYDBA@ORCL > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1607412806
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 729 | 121 (1)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 44999 | 3559K| 121 (1)| 00:00:02 |
|* 3 | SORT ORDER BY STOPKEY| | 44999 | 3471K| 121 (1)| 00:00:02 |
| 4 | TABLE ACCESS FULL | TEST | 44999 | 3471K| 121 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
3 - filter(ROWNUM<10)
17 rows selected.
Elapsed: 00:00:00.36
MYDBA@ORCL >
MYDBA@ORCL > set autotrace on statistics;
MYDBA@ORCL > select * from ( select * from test order by d desc) where rownum < 10;
D DATA
-------------------- ----------------------------------------------------------------------
30-AUG-2032 10:50:33 x
30-AUG-2032 07:13:43 x
30-AUG-2032 03:17:01 x
29-AUG-2032 21:37:38 x
29-AUG-2032 16:26:20 x
29-AUG-2032 13:44:17 x
29-AUG-2032 13:03:14 x
28-AUG-2032 20:27:48 x
28-AUG-2032 19:32:53 x
9 rows selected.
Elapsed: 00:00:00.16
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
528 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
507 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
MYDBA@ORCL > set autotrace off;
MYDBA@ORCL >
MYDBA@ORCL > explain plan for
2 select /*+ INDEX (test testidx) */ * from test where rownum < 10;
Explained.
Elapsed: 00:00:00.02
MYDBA@ORCL > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3931117773
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 711 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| TEST | 9 | 711 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
14 rows selected.
Elapsed: 00:00:00.05
MYDBA@ORCL >
MYDBA@ORCL > set autotrace on statistics;
MYDBA@ORCL > select /*+ INDEX (test testidx) */ * from test where rownum < 10;
D DATA
-------------------- ----------------------------------------------------------------------
21-AUG-2008 19:41:50 x
06-DEC-2011 20:37:24 x
21-SEP-2007 00:09:00 x
30-AUG-2008 06:06:37 x
09-FEB-2030 12:44:30 x
14-FEB-2016 16:01:33 x
22-SEP-2029 00:53:17 x
27-MAY-2008 06:50:14 x
12-JAN-2007 05:35:13 x
9 rows selected.
Elapsed: 00:00:00.02
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
507 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
MYDBA@ORCL > set autotrace off;
MYDBA@ORCL >
MYDBA@ORCL > explain plan for
2 select /*+ INDEX (test testidx) */ * from test where d > sysdate and rownum < 10;
Explained.
Elapsed: 00:00:00.03
MYDBA@ORCL > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1671284200
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 711 | 11 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 9 | 711 | 11 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TESTIDX | 44999 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
3 - access(SYS_OP_DESCEND("D")<SYS_OP_DESCEND(SYSDATE@!))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("D"))>SYSDATE@!)
17 rows selected.
Elapsed: 00:00:00.05
MYDBA@ORCL >
MYDBA@ORCL > set autotrace on statistics;
MYDBA@ORCL > select /*+ INDEX (test testidx) */ * from test where d > sysdate and rownum < 10;
D DATA
-------------------- ----------------------------------------------------------------------
30-AUG-2032 10:50:33 x
30-AUG-2032 07:13:43 x
30-AUG-2032 03:17:01 x
29-AUG-2032 21:37:38 x
29-AUG-2032 16:26:20 x
29-AUG-2032 13:44:17 x
29-AUG-2032 13:03:14 x
28-AUG-2032 20:27:48 x
28-AUG-2032 19:32:53 x
9 rows selected.
Elapsed: 00:00:00.05
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
1 physical reads
0 redo size
674 bytes sent via SQL*Net to client
507 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
MYDBA@ORCL > set autotrace off;
MYDBA@ORCL >
MYDBA@ORCL > set timing off;
MYDBA@ORCL >
MYDBA@ORCL > drop index testidx;
Index dropped.
MYDBA@ORCL > truncate table test;
Table truncated.
MYDBA@ORCL >
MYDBA@ORCL > insert /*+ append */ into test
2 select sysdate + dbms_random.value(1,10000), 'x'
3 from all_objects where rownum < 5000;
4999 rows created.
MYDBA@ORCL > commit;
Commit complete.
MYDBA@ORCL >
MYDBA@ORCL > create index testidx on test(d desc);
Index created.
MYDBA@ORCL >
MYDBA@ORCL > exec dbms_stats.gather_table_stats(user,'test',cascade=>true);
PL/SQL procedure successfully completed.
MYDBA@ORCL >
MYDBA@ORCL > set timing on;
MYDBA@ORCL >
MYDBA@ORCL > explain plan for
2 select * from ( select * from test order by d desc) where rownum < 10;
Explained.
Elapsed: 00:00:00.02
MYDBA@ORCL > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1607412806
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 729 | 16 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 4999 | 395K| 16 (0)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY| | 4999 | 385K| 16 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST | 4999 | 385K| 16 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
3 - filter(ROWNUM<10)
17 rows selected.
Elapsed: 00:00:00.05
MYDBA@ORCL >
MYDBA@ORCL > set autotrace on statistics;
MYDBA@ORCL > select * from ( select * from test order by d desc) where rownum < 10;
D DATA
-------------------- ----------------------------------------------------------------------
30-AUG-2032 09:34:24 x
28-AUG-2032 00:14:38 x
27-AUG-2032 11:42:47 x
22-AUG-2032 17:45:24 x
16-AUG-2032 20:47:48 x
12-AUG-2032 06:36:37 x
11-AUG-2032 07:20:33 x
11-AUG-2032 05:20:51 x
11-AUG-2032 04:38:47 x
9 rows selected.
Elapsed: 00:00:00.04
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
62 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
507 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
MYDBA@ORCL > set autotrace off;
MYDBA@ORCL >
MYDBA@ORCL > explain plan for
2 select /*+ INDEX (test testidx) */ * from test where rownum < 10;
Explained.
Elapsed: 00:00:00.02
MYDBA@ORCL > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3931117773
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 711 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| TEST | 9 | 711 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
14 rows selected.
Elapsed: 00:00:00.05
MYDBA@ORCL >
MYDBA@ORCL > set autotrace on statistics;
MYDBA@ORCL > select /*+ INDEX (test testidx) */ * from test where rownum < 10;
D DATA
-------------------- ----------------------------------------------------------------------
01-FEB-2026 12:09:55 x
14-MAR-2008 23:29:56 x
24-SEP-2028 03:57:46 x
12-SEP-2028 04:49:24 x
09-DEC-2015 06:51:21 x
28-APR-2010 17:30:39 x
17-OCT-2014 05:49:06 x
24-MAR-2025 21:34:36 x
05-SEP-2029 04:57:51 x
9 rows selected.
Elapsed: 00:00:00.02
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
507 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
MYDBA@ORCL > set autotrace off;
MYDBA@ORCL >
MYDBA@ORCL > explain plan for
2 select /*+ INDEX (test testidx) */ * from test where d > sysdate and rownum < 10;
Explained.
Elapsed: 00:00:00.02
MYDBA@ORCL > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1671284200
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 711 | 11 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 9 | 711 | 11 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TESTIDX | 4999 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
3 - access(SYS_OP_DESCEND("D")<SYS_OP_DESCEND(SYSDATE@!))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("D"))>SYSDATE@!)
17 rows selected.
Elapsed: 00:00:00.05
MYDBA@ORCL >
MYDBA@ORCL > set autotrace on statistics;
MYDBA@ORCL > select /*+ INDEX (test testidx) */ * from test where d > sysdate and rownum < 10;
D DATA
-------------------- ----------------------------------------------------------------------
30-AUG-2032 09:34:24 x
28-AUG-2032 00:14:38 x
27-AUG-2032 11:42:47 x
22-AUG-2032 17:45:24 x
16-AUG-2032 20:47:48 x
12-AUG-2032 06:36:37 x
11-AUG-2032 07:20:33 x
11-AUG-2032 05:20:51 x
11-AUG-2032 04:38:47 x
9 rows selected.
Elapsed: 00:00:00.39
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
1 physical reads
0 redo size
674 bytes sent via SQL*Net to client
507 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
MYDBA@ORCL > set autotrace off;
MYDBA@ORCL >
MYDBA@ORCL > set timing off;
MYDBA@ORCL >
MYDBA@ORCL > drop table test;
Table dropped.
MYDBA@ORCL >
MYDBA@ORCL > set echo off;
|
|
|
|
| Re: Last n records based on date [message #115859 is a reply to message #115723] |
Thu, 14 April 2005 13:58 |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
Barbara,
First, thanks much for the links. They were exactly what I was looking for. I am familiar with the thread at:
http://asktom.oracle.com/pls/ask/f?p=4950:8:4100624008093649519::NO::F4950_P8_DISPLAYID,F4950_P8_B:127412348064,Y
but I got a bit lost in the pages and pages and pages of that thread. The "Getting Just the Rows I Want" article was just the info I wanted. The rest of this post is miscellaneous ramblings. Read at your own risk.
I am a bit confused about your comment, "A hint is just a hint, not a command, so Oracle may choose to ignore it." I am not exactly a newbie. I have been working with Oracle Applications for about 8 years now and have written 3 completely custom, albeit small, applications (some might legitimately call them complamentary modules). I have written hundreds, if not thousands of queries. It just so happens that I tend to learn only what I need to get the job done (call it a personality flaw, my eyes glaze over when reading random technical info) and I have never needed hints. The CBO always seemed to do the job well enough. (Yes, we have had performance analysts on site at every project on which I have worked. They never kick back my queries, so I have just always assumed they were OK.) But I thought the whole purpose of a hint was to override the CBO when you, as the developer, KNEW it wasn't performing the query optimally. That is to say, a hint is not just a hint. A hint is a command to tell the parser that you know what is best and just do it. If it isn't a command, what is the point? The Performance Tuning Guide certainly seems to imply that they are commands. (I have Expert One on One on order, so if this is discussed in there, there is no need for you to respond. I can certainly do my own homework.)
I understand that you were responding to the question of "order by" and "The only reliable method of ordering rows is to use an order by clause." So I accept your answer in that context. But (always the "but"), if it were the case that hints were commands, would it logically follow that the hint would order the results. In eight years of doing this stuff, I have never noticed a single instance in which an index was used and the results didn't "magically" display in the order of the index.
Your other comment was also thought provoking. The interviewer was indeed "selecting just a few rows from a large table." But I find the completion of that sentence a bit problematic, "and in that instance Oracle happened to use the index and the desired result was obtained." Let me state right now that I have been reading this forum off and on for a couple of years and I have the greatest respect for your knowledge of Oracle. I specifically look for responses from you, Todd Barry, Maaher, Art Metzer and a couple of others because you all have been consistently right on point. Having said that, I know you know that Oracle just doesn't "happen" to use an index. There is a line of code somewhere in the database engine that says "if (x < y) {...} else {...}". IF I know the table is large, and IF I know the results are small, why couldn't I say with absolute certainty that I know the results will always be what I expect, i.e. that it will use the index and the results will be ordered. I know I should always write code that will work in all situations, i.e. independent of table size. I know that the "order by" clause creates no extra work for the database. I see that in the Tom Kyte's paper. I know that the cost/benefit trade off of elegance vs. robustness will never tilt toward elegance in this situation. I will obviously always use Kyte's method. But this is not about that right now. It is about the truth and the way computers programs work. If I know the relevant parameters will remain within acceptable bounds, why am I not justified in expecting the exact same results, every time?
Lastly, I am pretty sure the interviewer believed what he was saying was correct. But that's irrelevant. The problem was that I didn't know that he wasn't correct. I blew it. I accept that. I guess I need to study more of the stuff that I don't use on a daily basis. I don't feel that I have any legitimate cause to appeal. That being said, I am consistently amazed that there seems to be so many people out there who think that the way to determine whether somebody is a good PL/SQL developer is by asking simple knowledge questions that can easily be looked up and/or appear in one out of every thousand PL/SQL packages currently in production environments. But that's just a bit of sour grapes I guess.
Thanks again.
[Updated on: Thu, 14 April 2005 14:03] Report message to a moderator |
|
|
|
| Re: Last n records based on date [message #115866 is a reply to message #115859] |
Thu, 14 April 2005 14:21 |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
|
I got the same results as smartin and pscjhe. If I used the faux limit in the where clause, it would use the index and order the results without an "order by". But this still implies that the hint serves absolutely no purpose whatsoever. Add it or remover, it never changes anything.
|
|
|
|
| Re: Last n records based on date [message #115888 is a reply to message #115843] |
Thu, 14 April 2005 17:01 |
pscjhe
Messages: 38
Registered: April 2005
|
Member |
|
|
Thanks smartin for showing sql-way of populating data, well, I am not even a "local" expert and am learning along the way. it is really descending index not reverse key index.
The reason that your query does full scan, either at index range or table scan is because CBO didn't use the ROWID embeded in b-tree. Can we force it to use it ? yes, we probably can. It simply is not going to make query look nice any more. But it does reduce consistent reads significantly. Here is one way I did.
tch@TCHTST2> insert /*+ append */ into my_test select object_id, sysdate+dbms_random.value(1,1000) from all_objects;
tch@TCHTST2> commit;
Commit complete.
tch@TCHTST2> select * from my_test a, (select /*+ index_desc(my_test my_index) *
/ rowid rid from my_test where date1 > sysdate and rownum < 10) b where a.rowid=
b.rid;
COL1 DATE1 RID
---------- ----------------- ------------------
36340 01/09/08 15:19:06 AAAKhZAAdAAAAAbAEl
26335 01/09/08 14:43:56 AAAKhZABAAAAABAABu
34977 01/09/08 14:40:51 AAAKhZAADAAAAJhAAZ
24896 01/09/08 14:37:37 AAAKhZAADAAAAJkADv
20005 01/09/08 14:16:35 AAAKhZABAAAAAAzAEy
25901 01/09/08 13:52:36 AAAKhZAAdAAAAAlAEU
37833 01/09/08 13:21:36 AAAKhZAAdAAAAAhAEG
21641 01/09/08 13:16:49 AAAKhZAADAAAAJjACc
20677 01/09/08 13:04:31 AAAKhZAAdAAAAAfACE
9 rows selected.
Elapsed: 00:00:00.72
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=9 Bytes=351)
1 0 NESTED LOOPS (Cost=23 Card=9 Bytes=351)
2 1 VIEW (Cost=14 Card=9 Bytes=63)
3 2 COUNT (STOPKEY)
4 3 INDEX (RANGE SCAN DESCENDING) OF 'MY_INDEX' (NON-UNIQUE) (Cost
=14 Card=250 Bytes=3500)
5 1 TABLE ACCESS (BY USER ROWID) OF 'MY_TEST' (Cost=1 Card=1 Bytes=32)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
5 physical reads
120 redo size
813 bytes sent via SQL*Net to client
511 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
tch@TCHTST2> select * from ( select * from my_test order by date1 desc ) where r
ownum < 10;
COL1 DATE1
---------- -----------------
36340 01/09/08 15:19:06
26335 01/09/08 14:43:56
34977 01/09/08 14:40:51
24896 01/09/08 14:37:37
20005 01/09/08 14:16:35
25901 01/09/08 13:52:36
37833 01/09/08 13:21:36
21641 01/09/08 13:16:49
20677 01/09/08 13:04:31
9 rows selected.
Elapsed: 00:00:00.68
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=9 Bytes=198)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=8 Card=500 Bytes=11000)
3 2 SORT (ORDER BY STOPKEY) (Cost=8 Card=500 Bytes=14000)
4 3 TABLE ACCESS (FULL) OF 'MY_TEST' (Cost=2 Card=500 Bytes=14000)
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
146 consistent gets
60 physical reads
40 redo size
609 bytes sent via SQL*Net to client
511 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
|
|
|
|
|
|
| Re: Last n records based on date [message #115906 is a reply to message #115859] |
Fri, 15 April 2005 02:13 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Scott Mackey,
When you provide a hint, Oracle applies a heavier weight to what you have suggested with your hint than it would without the hint, but that may not be sufficient to outweigh other factors. You have already proven yourself that Oracle does not just obey the hint as an order or command.
Unfortunately, there are places in the documentation and such that say or imply that providing a hint will force usage of an index or whatever your hint has suggested. You have already proven this is wrong. Consider it a documentation bug.
In the days of the rule-based optimizer (RBO) hints were much more important and powerful than with the cost-based optimizer (CBO). With CBO, Oracle's internal algorithms are written such that it figures it may know more than you and saves you from yourself. With CBO, you will find Tom Kyte recommending usage of only a few hints, like first_rows and not recommending index hints. If your tables, indexes, and indexed columns are properly and recently analyzed, the optimizer is sophisticated enough to choose the best plan for you.
My choice of words, in saying that Oracle "happened" to use the index may have been confusing. It might have been clearer to say that it chose to use the index under those specific circumstances. Obviously, there is a precise set of complex algorithms that determines when Oracle does and does not do what. Cardinality is one of the major factors. There are many others. So, in that situtation, the factors were such that Oracle chose to use the index. If you knew exactly what all of those factors were, then you could predict what the execution plan of any statement would be, at that point in time. But, as the data changes, so might the execution plan. And, with the next version, things might be entirely different.
I hope this helps clarify things somewhat. A lot of the wording that I use, like "a hint is just a hint", are things that I have either heard from Tom Kyte or someone like the Oracle 8i SQL Statement Tuning workshop instructor who taught the same principles. I'm glad to hear you ordered Tom's book. Happy reading!
Barbara
[Updated on: Fri, 15 April 2005 02:16] Report message to a moderator |
|
|
|
| Re: Last n records based on date [message #115978 is a reply to message #115906] |
Fri, 15 April 2005 12:13 |
pscjhe
Messages: 38
Registered: April 2005
|
Member |
|
|
[QUOTE]
a hint is just a hint
[QUOTE]
Is it true or we simply don't understand hint? Probably both. But what comes first is that we DON't understand hint and Oracle well ennough to say so. Let's dig further in this matter.
Start with my oracle 9iR2 database.
tch@TCHTST2> create table t as select object_id id, sysdate+dbms_random.value(1,
1000) dt from all_objects;
Table created.
tch@TCHTST2> create index t_ndx on t ( dt desc );
Index created.
tch@TCHTST2> @explain "select /*+ index (t t_ndx) */ * from t where rownum < 10"
;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 198 | 12 |
|* 1 | COUNT STOPKEY | | | | |
| 2 | TABLE ACCESS FULL | T | 5310 | 114K| 12 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
Note: cpu costing is off
15 rows selected.
Now watch the difference
tch@TCHTST2> alter table t modify ( dt not null);
Table altered.
tch@TCHTST2> @explain "select /*+ index (t t_ndx) */ * from t where rownum < 10"
;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 198 | 826 |
|* 1 | COUNT STOPKEY | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 5310 | 114K| 826 |
| 3 | INDEX FULL SCAN | T_NDX | 5310 | | 26 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
Note: cpu costing is off
16 rows selected.
tch@TCHTST2> @explain "select /*+ index (t t_ndx) */ * from t";
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5310 | 114K| 826 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 5310 | 114K| 826 |
| 2 | INDEX FULL SCAN | T_NDX | 5310 | | 26 |
---------------------------------------------------------------------------
Note: cpu costing is off
10 rows selected.
tch@TCHTST2> @explain "select /*+ index (t t_ndx) */ count(*) from t";
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 26 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN | T_NDX | 5310 | | 26 |
--------------------------------------------------------------------
Note: cpu costing is off
10 rows selected.
The hint is WELL taken! The difference is if indexed column is not null, index FULL scan will be used. My earlier post was using "dt > xx" to "force" CBO uses index RANGE scan. Index RANGE scan starts from top of b-tree while index FULL scan go from either end of leaf nodes. We all know null's are not kept in b-tree. When our column is Nullable, if CBO uses FULL scan for "dt >xx", it will not fetch ALL the data, which is required by FULL. But RANGE scan doesn't imply to fetch ALL the data. So given certain conditions in predicates, it will use the index, check out these conditions in our lovely "performance tuning guide", read "index scan","index hint". This is why when I used "dt >xx" index RANGE scan is used. This makes perfect sense to me now.
Now we look at the difference between 9i and 10g. First 9i
tch@TCHTST2> @explain "select * from (select * from t order by dt) where rownum
< 10";
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | | | |
|* 3 | SORT ORDER BY STOPKEY| | | | |
| 4 | TABLE ACCESS FULL | T | | | |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
3 - filter(ROWNUM<10)
Note: rule based optimization
18 rows selected.
Now look at 10g
SQL> drop table t10g;
Table dropped.
SQL> create table t10g as select object_id id, sysdate+dbms_random.value(1,100) dt from all_objects;
Table created.
SQL> create index t_ndx on t10g(dt desc);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t10g',cascade=>true);
PL/SQL procedure successfully completed.
SQL> @explain "select /*+ index(t10g t_ndx) */ * from t10g where rownum < 10";
old 1: explain plan for &1
new 1: explain plan for select /*+ index(t10g t_ndx) */ * from t10g where rownum < 10
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1445607783
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 117 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T10G | 9 | 117 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
1 - filter(ROWNUM<10)
14 rows selected.
SQL> alter table t10g modify ( dt not null);
Table altered.
SQL> @explain "select /*+ index(t10g t_ndx) */ * from t10g where rownum < 10";
old 1: explain plan for &1
new 1: explain plan for select /*+ index(t10g t_ndx) */ * from t10g where rownum < 10
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1392334447
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 117 | 11 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T10G | 9 | 117 | 11 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | T_NDX | 47407 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
15 rows selected.
SQL> @explain "select /*+ index(t10g t_ndx) */ * from t10g";
old 1: explain plan for &1
new 1: explain plan for select /*+ index(t10g t_ndx) */ * from t10g
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 826255615
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47407 | 601K| 47144 (1)| 00:09:26 |
| 1 | TABLE ACCESS BY INDEX ROWID| T10G | 47407 | 601K| 47144 (1)| 00:09:26 |
| 2 | INDEX FULL SCAN | T_NDX | 47407 | | 135 (1)| 00:00:02 |
-------------------------------------------------------------------------------------
9 rows selected.
SQL> @explain "select /*+ index(t10g t_ndx) */ count(*) from t10g";
old 1: explain plan for &1
new 1: explain plan for select /*+ index(t10g t_ndx) */ count(*) from t10g
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2676702897
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 135 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | INDEX FULL SCAN| T_NDX | 47407 | | 135 (1)| 00:00:02 |
--------------------------------------------------------------------------
9 rows selected.
SQL> @explain "select * from (select * from t10g order by dt desc) where rownum < 10";
old 1: explain plan for &1
new 1: explain plan for select * from (select * from t10g order by dt desc) where rownum < 10
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2318969053
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 198 | 11 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 9 | 198 | 11 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T10G | 47407 | 601K| 11 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | T_NDX | 9 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
16 rows selected.
SQL> spool off;
We see in 10g. NOT NULL has the same affect on index as in 9i. So hint sitll works. But for query of "select * from (select * from t order by dt desc) where rownum < 10", 10g wisely used T_NDX full scan even without hint, 9i doesn't. Isn't 10g better ?
Now let's look the obscure feature of "descending index". It is actually a FBI !! With the magical hint INDEX_DESC, it essentially will do index RANGE or FULL scan DESCENDINGly on normal index. So there is no need to use "descending index" at all except we have to learn to use INDEX_DESC. It works for even 8.1.7 that I have. Now we know the interviewer WAS right. It is us that not understand Oracle to the "expert one on one " level.
I learned a lot from this exercise by rereading Oracle and tom's books.
Last point, we all like Tom. But for me, I learn more from the way that TOM uses to prove his claim a lot more than any claims that he or Mr Don B. make. His claims stand true longer than Mr. Don B (some of his are just purely false). But Tom's methods of proving things in Oracle will be there for long time to come.
|
|
|
|
|
|
| Re: Last n records based on date [message #115989 is a reply to message #115716] |
Fri, 15 April 2005 14:36 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Hey pscjhe, I liked your post, and am really enjoying this thread and the posts from everyone here. Please don't let the following "yeahbut" cause you to think otherwise.
Double check in your test towards the end where you ran it against 9i and then 10g to compare. You may not have calculated statistics on the 9i table, because it looked like it said rule based optimizer in the explain plan output.
That being said, it makes total since that if the index is not null, that oracle has more info and more access paths at its disposal. I'll have to run some tests to see, but I would think that with that knowledge, oracle could take a query like:
select * from (select * from t order by dt) where rownum < 10
and have it use the index to get the results, WITHOUT having to put an index hint in there at all. I'd be curious the results of that on both 9i and 10g. Likewise, even with the index hint, I would think you wouldn't have to put the artificial dt > some_date criteria on there, because that, in effect, caused oracle to only care about rows where dt was not null.
Also, I thought I read from a reliable source (I don't actually know, and need to test this also) that an index range scan can actually go either direction in an index, regardless of whether or not the index is asc or desc, and regardless of any hints. It can do this because all of the leaf blocks have two way pointers to both the prior and the next leaf block.
In other words it could drill down through the branches and hit the highest value in the index, and then range scan towards a lower value and stop just as easily as it could drill down and hit the lower value and range scan towards a higher one.
And finally, I'm in the camp (at the moment at least, until I'm convinced to change positions) that specific access plan route hints, such as index, should be avoided whenever possible. Maybe there is a specific query or two that is a critical point in an application that has to squeeze every drop out of the lemon, but in most cases they cause additional maintenence as data changes and the application is altered.
They also make for more uncertainty (and thus more testing) when it comes time to upgrade, because oracle is constantly enhancing the CBO. Even minor version upgrades must be examined more closely than they possibly would otherwise, and I'm against anything that slows or hinders or gives people an excuse not to upgrade to the latest stable version possible.
|
|
|
|
| Re: Last n records based on date [message #115991 is a reply to message #115716] |
Fri, 15 April 2005 14:56 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Did a quick rerun of my earlier test above, but this time made the date field not null. It does in fact use the index, without having to use a hint. Note also that it does a full scan with the stopkey optimization, because this is not a situation where a range scan would come into play.
Of course this approach is only valid if your business logic allows you to set the dt field to not null. But if it does, it is in my view the best way to go, not only for the usual reasons, but also for the improved consistent gets shown below.
I won't repost the entire test, but here is just the one query:
MYDBA@ORCL >
MYDBA@ORCL > set timing on;
MYDBA@ORCL >
MYDBA@ORCL > explain plan for
2 select * from ( select * from test order by d desc) where rownum < 10;
Explained.
Elapsed: 00:00:00.03
MYDBA@ORCL > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1004210730
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 729 | 11 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 9 | 729 | 11 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST | 4999 | 385K| 11 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | TESTIDX | 9 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
16 rows selected.
Elapsed: 00:00:00.05
MYDBA@ORCL >
MYDBA@ORCL > set autotrace on statistics;
MYDBA@ORCL > select * from ( select * from test order by d desc) where rownum < 10;
D DATA
-------------------- ----------------------------------------------------------------------
28-AUG-2032 01:19:05 x
27-AUG-2032 21:34:17 x
21-AUG-2032 05:35:11 x
20-AUG-2032 00:07:34 x
13-AUG-2032 10:43:24 x
12-AUG-2032 22:10:22 x
08-AUG-2032 23:58:48 x
03-AUG-2032 00:44:38 x
02-AUG-2032 04:54:18 x
9 rows selected.
Elapsed: 00:00:00.03
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
MYDBA@ORCL > set autotrace off;
MYDBA@ORCL >
|
|
|
|
| Re: Last n records based on date [message #115992 is a reply to message #115989] |
Fri, 15 April 2005 15:03 |
pscjhe
Messages: 38
Registered: April 2005
|
Member |
|
|
The plan wasn't displayed right. Here is revisit at 9i.
tch@TCHTST2> drop index t_ndx;
Index dropped.
tch@TCHTST2> create index t_ndx on t(dt desc);
Index created.
tch@TCHTST2> @explain "select * from (select * from t order by dt desc) where r
wnum < 10";
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 198 | 98 |
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 26236 | 563K| 98 |
|* 3 | SORT ORDER BY STOPKEY| | 26236 | 307K| 98 |
| 4 | TABLE ACCESS FULL | T | 26236 | 307K| 12 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
3 - filter(ROWNUM<10)
Note: cpu costing is off
18 rows selected.
tch@TCHTST2> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL procedure successfully completed.
tch@TCHTST2> @explain "select * from (select * from t order by dt desc) where r
wnum < 10";
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 198 | 101 |
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 26236 | 563K| 101 |
|* 3 | SORT ORDER BY STOPKEY| | 26236 | 333K| 101 |
| 4 | TABLE ACCESS FULL | T | 26236 | 333K| 12 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
3 - filter(ROWNUM<10)
Note: cpu costing is off
18 rows selected.
tch@TCHTST2>
tch@TCHTST2> @explain "select * from (select /*+ index (t t_ndx) */ * from t or
er by dt desc) where rownum < 10";
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 198 | 25916 |
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 26236 | 563K| 25916 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 26236 | 333K| 25916 |
| 4 | INDEX FULL SCAN | T_NDX | 26236 | | 75 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
Note: cpu costing is off
17 rows selected.
Yes. both RANGE / FULL scan can be ASC or DESC to both directions at leaf nodes. Oracle books don't say where it starts for FULL scan. But tom's 2nd book on p461 saying FULL scan starts from TOP as well. Yes, If other pointers are kept to leaf head or tail, they have to be maitained whenever they are changed as b-tree does often. So Oracle chooses to keep on the pointer to root node, which is unchanged. (see I am learning )The key is still "NOT NULL" issue. In fact, Tom's first book has detailed discussion on p299.
The points that hints not be used in production/development are totally agreed. But hints are excellent tool for learning. And it does when CBO doesn't do the right job, such as the 9i case above.
|
|
|
|
| Re: Last n records based on date [message #140000 is a reply to message #115992] |
Fri, 30 September 2005 08:15 |
ziggy
Messages: 27
Registered: July 2005
Location: Moscow, Russia
|
Junior Member |
|
|
Difference between INDEX_DESC and ORDER BY is the choose problem between performance and reliability.
INDEX_DESC works faster, but the problem which Tom Kyte wrote as "timing bomb in your code" is what happens with such hints in future Oracle versions or in parallel query processing by example.
I think that the proposed solution with ORDER BY and rownum good enough or better to say that reliability enough.
Didn't you think about reliability of you solutions?
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 17:44:05 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
