CODEWITHSUNDEEP
oracle-database
Đặng Đình Bằng
Đặng Đình Bằng

Reputation: 29

why selecting data is very slow

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?

Upvotes: 0

Views: 53

Answers (1)

CompEng
CompEng

Reputation: 7376

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');

Upvotes: 1

Related Questions