0

can some one help me to resolve problem. i have partion table ( F_BUNDLCOL for P5,6,7 and 8). i dont know why when i select data from P5, 7,8 it very fast ( only 0.26 seconds)

SELECT ri,rowid FROM creactor.F_BUNDLCOL WHERE part=5 AND status='0' and rownum<10

        RI ROWID
---------- ---------------------------------------------------------------------------
    227122 *BAXAClgCwQYBMATDF0gX/g
    227125 *BAXAClgCwQYBMATDF0ga/g
    227148 *BAXAClgCwQYBMATDF0gx/g
    227187 *BAXAClgCwQYBMATDF0hY/g
    227238 *BAXAClgCwQYBMATDF0kn/g
    227313 *BAXAClgCwQYBMATDF0oO/g
    227371 *BAXAClgCwQYBMATDF0pI/g
    227503 *BAXAClgCwQYBMATDF0wE/g
    227514 *BAXAClgCwQYBMATDF0wP/g

9 rows selected.

Elapsed: 00:00:00.26

but with P6, it take me 8 seconds to finish.

SELECT ri,rowid FROM creactor.F_BUNDLCOL WHERE part=6 AND status='0' and rownum<10

        RI ROWID
---------- ---------------------------------------------------------------------------
   3018728 *BBHAIU8CwQcBMAXEBAJYHf4
   3019001 *BBHAIU8CwQcBMAXEBAJbAv4
   3019535 *BBHAIU8CwQcBMAXEBAJgJP4
   3019565 *BBHAIU8CwQcBMAXEBAJgQv4
   3019681 *BBHAIU8CwQcBMAXEBAJhUv4
   3020394 *BBHAIU8CwQcBMAXEBAMEX/4
   3020451 *BBHAIU8CwQcBMAXEBAMFNP4
   3020629 *BBHAIU8CwQcBMAXEBAMHHv4
   3020836 *BBHAIU8CwQcBMAXEBAMJJf4

9 rows selected.

table is the same, all index are the same. can some one help to where i sholud to check?

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
Đặng Đình Bằng
  • 21
  • 6
  • Do you see any difference in the SQL execution plans of the two queries you have posted? – ArtBajji Oct 31 '19 at 08:36
  • Basically *no one* can seriosly answer your question based on the (limited) observation you provided without *wild guessing*. Why do you thing the information that *indexes are the same* would help us if we do not know that the indexes are used? See [here](https://stackoverflow.com/a/34975420/4808122) the minimum information you should provide. And my *wild guess* you make a `full partition scan` and your data have a different oder of the `status` column, so in some partions you have to skip lot of row before you fetch 10 rows with zero status. – Marmite Bomber Oct 31 '19 at 08:51
  • it have same indexes.INDEX_NAME PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ PK_F_BUNDLCOL P5 CREACTOR_D5 PK_F_BUNDLCOL P6 CREACTOR_D6 PK_F_BUNDLCOL P7 CREACTOR_D7 PK_F_BUNDLCOL P8 CREACTOR_D8 – Đặng Đình Bằng Oct 31 '19 at 09:09

1 Answers1

1

can you look table partition statistics.

get istatistics for P6 partition.

like below:

DBMS_STATS.GATHER_TABLE_STATS('YOUR_SCHEMA','YOUR_TABLE_NAME',PARTNAME=>'YOUR_PARTITION_NAME',GRANULARITY=>'PARTITION');
CompEng
  • 7,161
  • 16
  • 68
  • 122