OraFAQ Forum: Performance Tuning » unstable sort is inapprehensible (merged)

Home » RDBMS Server » Performance Tuning » unstable sort is inapprehensible (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

unstable sort is inapprehensible (merged) [message #344264]

Fri, 29 August 2008 04:36

stronghearted
Messages: 4
Registered: August 2007

Junior Member

i have table named "tbf_thread" with two indexes "IDX_THREAD_BBS_LIST1" and "IDX_THREAD_BBS_LIST2":

IDX_THREAD_BBS_LIST1 FORUM_ID 1 VALID
IDX_THREAD_BBS_LIST1 CREATION_DATE 2 VALID
IDX_THREAD_BBS_LIST1 THREAD_TYPE 3 VALID
IDX_THREAD_BBS_LIST1 THREAD_CLASS 4 VALID
IDX_THREAD_BBS_LIST1 SET_PITH 5 VALID
IDX_THREAD_BBS_LIST1 SET_ORIGINAL 6 VALID
IDX_THREAD_BBS_LIST1 THREAD_EXTEND_TYPE 7 VALID
IDX_THREAD_BBS_LIST1 APPROACH 8 VALID
IDX_THREAD_BBS_LIST2 FORUM_ID 1 VALID
IDX_THREAD_BBS_LIST2 LAST_MSG_TIME 2 VALID
IDX_THREAD_BBS_LIST2 THREAD_TYPE 3 VALID
IDX_THREAD_BBS_LIST2 THREAD_CLASS 4 VALID
IDX_THREAD_BBS_LIST2 SET_PITH 5 VALID
IDX_THREAD_BBS_LIST2 SET_ORIGINAL 6 VALID
IDX_THREAD_BBS_LIST2 THREAD_EXTEND_TYPE 7 VALID
IDX_THREAD_BBS_LIST2 APPROACH 8 VALID

i have a sql:

16:40:05 SQL> set autot on
16:40:09 SQL> set linesize 300
16:40:15 SQL> select /*+ ordered use_nl(a t) */
16:40:24 2 count(*)
16:40:24 3 from (select *
16:40:24 4 from (select rid, rn
16:40:24 5 from (select rowid rid, rownum rn
16:40:24 6 from tbf_thread
16:40:24 7 where forum_id = 0
16:40:24 8 and (thread_type in (0, 3))
16:40:24 9 and THREAD_CLASS = 3217
16:40:24 10 AND (APPROACH > -1)
16:40:24 11 order by last_msg_time desc)
16:40:25 12 where rownum <= 100)
16:40:25 13 where rn >= 50) a,
16:40:25 14 tbf_thread t
16:40:25 15 where a.rid = t.rowid;

COUNT(*)
----------
67
1 row selected.

Elapsed: 00:00:08.56

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2351 Card=1 Bytes=27)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=2351 Card=100 Bytes=2700)
3 2 VIEW (Cost=2251 Card=100 Bytes=2000)
4 3 COUNT (STOPKEY)
5 4 VIEW (Cost=2251 Card=228 Bytes=4560)
6 5 SORT (ORDER BY STOPKEY) (Cost=2251 Card=228 Bytes=8664)
7 6 COUNT
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'TBF_THREAD' (Cost=2234 Card=228 Bytes=8664)
9 8 INDEX (RANGE SCAN) OF 'IDX_THREAD_BBS_LIST1' (NON-UNIQUE) (Cost=5465 Card=228)
10 2 TABLE ACCESS (BY USER ROWID) OF 'TBF_THREAD' (Cost=1 Card=1 Bytes=7)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17208 consistent gets
0 physical reads
0 redo size
491 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

The oracle use the IDX_THREAD_BBS_LIST1 ,not use the IDX_THREAD_BBS_LIST2.first,i think cbo will use IDX_THREAD_BBS_LIST2,but oracle use the IDX_THREAD_BBS_LIST1 in fact!I think some reasons result in:
index's CLUSTERING_FACTOR;wrong statistic info.

Any reason? could you tell me?thanks!this is first question.

I think the sql will reture 51 rows,but return 67 rows,i don't know what happend?why???

go on:

17:12:23 SQL> select /*+ ordered use_nl(a t) */
17:12:36 2 count(*)
17:12:36 3 from (select *
17:12:36 4 from (select rid, rn
17:12:36 5 from (select /*+ index(t IDX_THREAD_BBS_LIST2) */rowid rid, rownum rn
17:12:36 6 from tbf_thread t
17:12:36 7 where forum_id = 0
17:12:36 8 and (thread_type in (0, 3))
17:12:36 9 and THREAD_CLASS = 3217
17:12:36 10 AND (APPROACH > -1)
17:12:37 11 order by last_msg_time desc)
17:12:37 12 where rownum <= 100)
17:12:37 13 where rn >= 50) a,
17:12:37 14 tbf_thread t
17:12:37 15 where a.rid = t.rowid;

COUNT(*)
----------
85

1 row selected.

Elapsed: 00:00:08.06

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3372 Card=1 Bytes=27
)

1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=3372 Card=100 Bytes=2700)
3 2 VIEW (Cost=3272 Card=100 Bytes=2000)
4 3 COUNT (STOPKEY)
5 4 VIEW (Cost=3272 Card=228 Bytes=4560)
6 5 SORT (ORDER BY STOPKEY) (Cost=3272 Card=228 Bytes=8664)
7 6 COUNT
8 7 INDEX (RANGE SCAN) OF 'IDX_THREAD_BBS_LIST2'(NON-UNIQUE) (Cost=8138 Card=228 Bytes=8664)
9 2 TABLE ACCESS (BY USER ROWID) OF 'TBF_THREAD' (Cost=1 Card=1 Bytes=7)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17156 consistent gets
0 physical reads
0 redo size
491 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

i use the hint /*+ index(t IDX_THREAD_BBS_LIST2) */,the oracle reture the 85 rows,i get confused ,i always think the result will be 51 rows,because the index "IDX_THREAD_BBS_LIST2" including the "last_msg_time" colum!

why? the same codition,the different result?this is the second question.

thanks in advance!

Report message to a moderator

unstable sort is inapprehensible [message #344265 is a reply to message #344264]

Fri, 29 August 2008 04:36

stronghearted
Messages: 4
Registered: August 2007

Junior Member

16:40:05 SQL> set autot on
16:40:09 SQL> set linesize 300
16:40:15 SQL>  select /*+ ordered use_nl(a t) */
16:40:24   2   count(*)
16:40:24   3    from (select *
16:40:24   4            from (select rid, rn
16:40:24   5                    from (select  rowid rid, rownum rn
16:40:24   6                            from tbf_thread 
16:40:24   7                           where forum_id = 0
16:40:24   8                             and (thread_type in (0, 3))
16:40:24   9                             and THREAD_CLASS = 3217
16:40:24  10                             AND (APPROACH > -1)
16:40:24  11                           order by last_msg_time desc)
16:40:25  12                   where rownum <= 100)
16:40:25  13           where rn >= 50) a,
16:40:25  14         tbf_thread t
16:40:25  15  where a.rid = t.rowid;

 COUNT(*)
----------
        67
1 row selected.

Elapsed: 00:00:08.56

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2351 Card=1 Bytes=27)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=2351 Card=100 Bytes=2700)
   3    2       VIEW (Cost=2251 Card=100 Bytes=2000)
   4    3         COUNT (STOPKEY)
   5    4           VIEW (Cost=2251 Card=228 Bytes=4560)
   6    5             SORT (ORDER BY STOPKEY) (Cost=2251 Card=228 Bytes=8664)
   7    6               COUNT
   8    7                 TABLE ACCESS (BY INDEX ROWID) OF 'TBF_THREAD' (Cost=2234 Card=228 Bytes=8664)
   9    8                   INDEX (RANGE SCAN) OF [color=orangered]'IDX_THREAD_BBS_LIST1' [/color](NON-UNIQUE) (Cost=5465 Card=228)
  10    2       TABLE ACCESS (BY USER ROWID) OF 'TBF_THREAD' (Cost=1 Card=1 Bytes=7)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      17208  consistent gets
          0  physical reads
          0  redo size
        491  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

The oracle use the IDX_THREAD_BBS_LIST1 ,not use the IDX_THREAD_BBS_LIST2.first,i think cbo will use IDX_THREAD_BBS_LIST2,but oracle use the IDX_THREAD_BBS_LIST1 in fact!I think some reasons result in:
index's CLUSTERING_FACTOR;wrong statistic info.

Any reason? could you tell me?thanks!this is first question.

I think the sql will reture 51 rows,but return 67 rows,i don't know what happend?why???

go on:

17:12:23 SQL>  select /*+ ordered use_nl(a t) */
17:12:36   2   count(*)
17:12:36   3    from (select *
17:12:36   4            from (select rid, rn
17:12:36   5                    from (select  /*+ index(t IDX_THREAD_BBS_LIST2) */rowid rid, rownum rn
17:12:36   6                            from tbf_thread t
17:12:36   7                           where forum_id = 0
17:12:36   8                             and (thread_type in (0, 3))
17:12:36   9                             and THREAD_CLASS = 3217
17:12:36  10                             AND (APPROACH > -1)
17:12:37  11                           order by last_msg_time desc)
17:12:37  12                   where rownum <= 100)
17:12:37  13           where rn >= 50) a,
17:12:37  14         tbf_thread t
17:12:37  15  where a.rid = t.rowid;

COUNT(*)
----------
   85

1 row selected.

Elapsed: 00:00:08.06

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3372 Card=1 Bytes=27
          )

   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=3372 Card=100 Bytes=2700)
   3    2       VIEW (Cost=3272 Card=100 Bytes=2000)
   4    3         COUNT (STOPKEY)
   5    4           VIEW (Cost=3272 Card=228 Bytes=4560)
   6    5             SORT (ORDER BY STOPKEY) (Cost=3272 Card=228 Bytes=8664)
   7    6               COUNT
   8    7                 INDEX (RANGE SCAN) OF 'IDX_THREAD_BBS_LIST2'(NON-UNIQUE) (Cost=8138 Card=228 Bytes=8664)
   9    2       TABLE ACCESS (BY USER ROWID) OF 'TBF_THREAD' (Cost=1 Card=1 Bytes=7)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      17156  consistent gets
          0  physical reads
          0  redo size
        491  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

i use the hint /*+ index(t IDX_THREAD_BBS_LIST2) */,the oracle reture the 85 rows,i get confused ,i always think the result will be 51 rows,because the index "IDX_THREAD_BBS_LIST2" including the "last_msg_time" colum!

why? the same codition,the different result?this is the second question.

thanks in advance!

[Updated on: Fri, 29 August 2008 05:31]

Report message to a moderator

Re: unstable sort is inapprehensible [message #344360 is a reply to message #344265]

Fri, 29 August 2008 09:19

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

The innermost Select doesn't use the second column of either index, so there's nothing in it as to which one it picks - doesn't matter.

Your inner query is selecting some rows from the database, giving them each a number (rownum) and then ordering them. This ordering will destroy the rownum order - ie they won't come back 1,2,3,4....

Thus the rows that you get back in

SELECT rid,rn 
FROM (...) WHERE rownum <= 100

will not have rn values from 1-100.
Thus when you restrict your set to values with rn>=50, all you can say is that you will get less than 101 rows back.

Looking at your query, it makes no sense - after you extract these rowids from the inner query, you are doing a join to Tbf_Thread (the table the rowids came from) - every simgle rowid will match a ro in Tbf_Thread as that's where it came from.

Your query is the same as this:

select count(*)
from (select rid, rn
      from (select  /*+ index(t IDX_THREAD_BBS_LIST2) */rowid rid, rownum rn
            from tbf_thread t
            where forum_id = 0
            and (thread_type in (0, 3))
            and THREAD_CLASS = 3217
            AND (APPROACH > -1)
            order by last_msg_time desc)
      where rownum <= 100)
where rn >= 50);

but the whole thing is just a mess. I strongly doubt this query is doing what you think it's doing.

If you can explain what you're trying to do, I may be able to help further

Report message to a moderator

Re: unstable sort is inapprehensible [message #344682 is a reply to message #344360]

Mon, 01 September 2008 00:34

stronghearted
Messages: 4
Registered: August 2007

Junior Member

thanks for your help!

 select /*+ ordered use_nl(a t) */
 t.THREAD_ID as THREAD_ID,
 t.THREAD_CLASS as THREAD_CLASS,
 t.THREAD_TYPE as THREAD_TYPE,
 t.FORUM_ID as FORUM_ID,
 t.CREATION_DATE as CREATION_DATE,
 t.MODIFICATION_DATE as MODIFICATION_DATE,
 t.VALID_START_DATE as VALID_START_DATE,
 t.SUBJECT as SUBJECT,
 t.NEED_LVL as NEED_LVL,
 t.SET_LOCK as SET_LOCK,
 t.SET_PITH as SET_PITH,
 t.SET_ORIGINAL as SET_ORIGINAL,
 t.recommended as recommended,
 t.REPLIED as REPLIED,
 t.AUTHOR_ID as AUTHOR_ID,
 t.AUTHOR_NICKNAME as AUTHOR_NICKNAME,
 t.AUTHOR_IP as AUTHOR_IP,
 t.view_count,
 t.post_count,
 T.FIRST_MSG_ID,
 T.LAST_MSG_ID,
 T.LAST_MSG_TIME,
 T.LAST_USERID,
 T.LAST_NICKNAME,
 nvl(t.THREAD_EXTEND_TYPE, 0) THREAD_EXTEND_TYPE,
 t.has_reward,
 t.has_auction,
 t.product_id,
 t.TAGS,
 t.APPROACH,
 t.TABOOLEVEL,
 t.THREADAUDIT
  from (select *
          from (select rid, rn
                  from (select rowid rid, rownum rn
                          from tbf_thread t
                         where forum_id = 0
                           and (thread_type in (0, 3))
                           and THREAD_CLASS = 3217
                           AND (APPROACH > -1)
                         order by last_msg_time desc)
                 where rownum <= 100)
         where rn >= 50) a,
       tbf_thread t
where a.rid = t.rowid;

this is primary sentence,Paging sql,i'd like to get second page's attribute.51 rows per page(BBS).

how to write the paging sql?

[Updated on: Mon, 01 September 2008 00:49]

Report message to a moderator

Previous Topic:	DB parameters
Next Topic:	Query optimisation using meaningless condition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 30 16:12:05 CDT 2024