RE: select count(*) = 0, select /*+ full(a) */ count(*) = 5227 ?
Bernard I think this may be a bug in early versions of 8.1.7. I don't know the number, but I had a similar problem recently. Its to do with whether the query is scanning an index or not. Try forcing the query to do a full scan: - SQL select /*full(ced_info_mouvement)*/ count(*) from ced_info_mouvement; The only solution I found was to upgrade to 8.1.7.3+ HTH David Lord -Original Message- From: Bernard, Gilbert [mailto:[EMAIL PROTECTED]] Sent: 30 August 2002 14:23 To: Multiple recipients of list ORACLE-L Subject: select count(*) = 0, select /*+ full(a) */ count(*) = 5227 ? On Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production PL/SQL Release 8.1.7.2.0 - Production CORE8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I got a stange result and I wish to find an explanation. Look I did SQL select count(*) from ced_info_mouvement ; cls COUNT(*) -- 0 1 ligne sÚlectionnÚe. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 PARTITION HASH (ALL) 4 3 INDEX (FAST FULL SCAN) OF 'MVT_ID_LOT_IDX' (NON-UNIQUE) (Cost=2 Card=24507) Statistics -- 0 recursive calls 288 db block gets 120 consistent gets 0 physical reads 0 redo size 203 bytes sent via SQL*Net to client 248 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed I tryed with and order by on the first column_name, I got the exact number of rows in this table, why ? SQL select count(*) from ced_info_mouvement order by IDENTIF_PRODUIT_COMPTABLE; cls COUNT(*) -- 5227 1 ligne sÚlectionnÚe. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=17) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 PARTITION HASH (ALL) 4 3 TABLE ACCESS (FULL) OF 'CED_INFO_MOUVEMENT' (Cost=19 Card=24507 Bytes=416619) Statistics -- 0 recursive calls 116 db block gets 246 consistent gets 0 physical reads 0 redo size 206 bytes sent via SQL*Net to client 248 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed I tryed vith HINTS FULL and I got 5227 rows. Regards. NAME VALUE - - active_instance_count always_anti_join NESTED_LOOPS always_semi_join standard aq_tm_processes 0 audit_file_dest ?/rdbms/audit audit_trail NONE background_core_dump partial background_dump_dest /sscedre/data/sqcedi/admin/log backup_tape_io_slaves FALSE bitmap_merge_area_size1048576 blank_trimmingFALSE buffer_pool_keep buffer_pool_recycle commit_point_strength 1 compatible8.1.0 control_file_record_keep_time 7 control_files /sscedre/data/sqcedi/disk1/ctrl11sqcedi.ctl, /sscedre/data/sqcedi/disk2/ctrl12sqcedi.ctl, /sscedre/data/sqcedi/disk3/ctrl13sqcedi.ctl core_dump_dest?/dbs cpu_count 4 create_bitmap_area_size 8388608 cursor_sharingEXACT cursor_space_for_time FALSE db_block_buffers 3200 db_block_checking FALSE db_block_checksum FALSE db_block_lru_latches 2 db_block_max_dirty_target 3200 db_block_size 8192 db_domain db_file_direct_io_count 64 db_file_multiblock_read_count 32 db_file_name_convert db_files
RE: select count(*) = 0, select /*+ full(a) */ count(*) = 5227 ?
Rebuild your index and try again -Original Message- Sent: Friday, August 30, 2002 6:53 PM To: Multiple recipients of list ORACLE-L ? On Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production PL/SQL Release 8.1.7.2.0 - Production CORE8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I got a stange result and I wish to find an explanation. Look I did SQL select count(*) from ced_info_mouvement ; cls COUNT(*) -- 0 1 ligne sÚlectionnÚe. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 PARTITION HASH (ALL) 4 3 INDEX (FAST FULL SCAN) OF 'MVT_ID_LOT_IDX' (NON-UNIQUE) (Cost=2 Card=24507) Statistics -- 0 recursive calls 288 db block gets 120 consistent gets 0 physical reads 0 redo size 203 bytes sent via SQL*Net to client 248 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed I tryed with and order by on the first column_name, I got the exact number of rows in this table, why ? SQL select count(*) from ced_info_mouvement order by IDENTIF_PRODUIT_COMPTABLE; cls COUNT(*) -- 5227 1 ligne sÚlectionnÚe. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=17) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 PARTITION HASH (ALL) 4 3 TABLE ACCESS (FULL) OF 'CED_INFO_MOUVEMENT' (Cost=19 Card=24507 Bytes=416619) Statistics -- 0 recursive calls 116 db block gets 246 consistent gets 0 physical reads 0 redo size 206 bytes sent via SQL*Net to client 248 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed I tryed vith HINTS FULL and I got 5227 rows. Regards. NAME VALUE - - active_instance_count always_anti_join NESTED_LOOPS always_semi_join standard aq_tm_processes 0 audit_file_dest ?/rdbms/audit audit_trail NONE background_core_dump partial background_dump_dest /sscedre/data/sqcedi/admin/log backup_tape_io_slaves FALSE bitmap_merge_area_size1048576 blank_trimmingFALSE buffer_pool_keep buffer_pool_recycle commit_point_strength 1 compatible8.1.0 control_file_record_keep_time 7 control_files /sscedre/data/sqcedi/disk1/ctrl11sqcedi.ctl, /sscedre/data/sqcedi/disk2/ctrl12sqcedi.ctl, /sscedre/data/sqcedi/disk3/ctrl13sqcedi.ctl core_dump_dest?/dbs cpu_count 4 create_bitmap_area_size 8388608 cursor_sharingEXACT cursor_space_for_time FALSE db_block_buffers 3200 db_block_checking FALSE db_block_checksum FALSE db_block_lru_latches 2 db_block_max_dirty_target 3200 db_block_size 8192 db_domain db_file_direct_io_count 64 db_file_multiblock_read_count 32 db_file_name_convert db_files 500 dblink_encrypt_login FALSE db_name sqcedi dbwr_io_slaves0 db_writer_processes 1 disk_asynch_ioTRUE distributed_transactions 23 dml_locks 500 enqueue_resources 5000 event 10262 trace name context forever,level 4096 fast_start_io_target 3200 fast_start_parallel_rollback LOW
RE: select count(*) = 0, select /*+ full(a) */ count(*) = 5227 ?
MVT_ID_LOT_IDX is corrupt? Does rebuilding the index fix the problem? -Original Message- Sent: Friday, August 30, 2002 9:23 AM To: Multiple recipients of list ORACLE-L ? On Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production PL/SQL Release 8.1.7.2.0 - Production CORE8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I got a stange result and I wish to find an explanation. Look I did SQL select count(*) from ced_info_mouvement ; cls COUNT(*) -- 0 1 ligne sÚlectionnÚe. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 PARTITION HASH (ALL) 4 3 INDEX (FAST FULL SCAN) OF 'MVT_ID_LOT_IDX' (NON-UNIQUE) (Cost=2 Card=24507) Statistics -- 0 recursive calls 288 db block gets 120 consistent gets 0 physical reads 0 redo size 203 bytes sent via SQL*Net to client 248 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed I tryed with and order by on the first column_name, I got the exact number of rows in this table, why ? SQL select count(*) from ced_info_mouvement order by IDENTIF_PRODUIT_COMPTABLE; cls COUNT(*) -- 5227 1 ligne sÚlectionnÚe. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=17) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 PARTITION HASH (ALL) 4 3 TABLE ACCESS (FULL) OF 'CED_INFO_MOUVEMENT' (Cost=19 Card=24507 Bytes=416619) Statistics -- 0 recursive calls 116 db block gets 246 consistent gets 0 physical reads 0 redo size 206 bytes sent via SQL*Net to client 248 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed I tryed vith HINTS FULL and I got 5227 rows. Regards. NAME VALUE - - active_instance_count always_anti_join NESTED_LOOPS always_semi_join standard aq_tm_processes 0 audit_file_dest ?/rdbms/audit audit_trail NONE background_core_dump partial background_dump_dest /sscedre/data/sqcedi/admin/log backup_tape_io_slaves FALSE bitmap_merge_area_size1048576 blank_trimmingFALSE buffer_pool_keep buffer_pool_recycle commit_point_strength 1 compatible8.1.0 control_file_record_keep_time 7 control_files /sscedre/data/sqcedi/disk1/ctrl11sqcedi.ctl, /sscedre/data/sqcedi/disk2/ctrl12sqcedi.ctl, /sscedre/data/sqcedi/disk3/ctrl13sqcedi.ctl core_dump_dest?/dbs cpu_count 4 create_bitmap_area_size 8388608 cursor_sharingEXACT cursor_space_for_time FALSE db_block_buffers 3200 db_block_checking FALSE db_block_checksum FALSE db_block_lru_latches 2 db_block_max_dirty_target 3200 db_block_size 8192 db_domain db_file_direct_io_count 64 db_file_multiblock_read_count 32 db_file_name_convert db_files 500 dblink_encrypt_login FALSE db_name sqcedi dbwr_io_slaves0 db_writer_processes 1 disk_asynch_ioTRUE distributed_transactions 23 dml_locks 500 enqueue_resources 5000 event 10262 trace name context forever,level 4096 fast_start_io_target 3200
RE: select count(*) = 0, select /*+ full(a) */ count(*) = 5227 ?
There was a bug (still is in 8?) in 7.3 where a parallel index creation would cause an index to be built with 0 entries. This would occur when the index columns could be found in another index. The parallel master process would get confused as to which index was the source and target. The end result...an index with 0 entries. I still fondly recall the panicked call from a developer 'I created an index and it deleted all the rows!'. -Original Message- Sent: Friday, August 30, 2002 8:09 AM To: Multiple recipients of list ORACLE-L 5227 ? MVT_ID_LOT_IDX is corrupt? Does rebuilding the index fix the problem? -Original Message- Sent: Friday, August 30, 2002 9:23 AM To: Multiple recipients of list ORACLE-L ? On Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production PL/SQL Release 8.1.7.2.0 - Production CORE8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I got a stange result and I wish to find an explanation. Look I did SQL select count(*) from ced_info_mouvement ; cls COUNT(*) -- 0 1 ligne sÚlectionnÚe. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 PARTITION HASH (ALL) 4 3 INDEX (FAST FULL SCAN) OF 'MVT_ID_LOT_IDX' (NON-UNIQUE) (Cost=2 Card=24507) Statistics -- 0 recursive calls 288 db block gets 120 consistent gets 0 physical reads 0 redo size 203 bytes sent via SQL*Net to client 248 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed I tryed with and order by on the first column_name, I got the exact number of rows in this table, why ? SQL select count(*) from ced_info_mouvement order by IDENTIF_PRODUIT_COMPTABLE; cls COUNT(*) -- 5227 1 ligne sÚlectionnÚe. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=17) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 PARTITION HASH (ALL) 4 3 TABLE ACCESS (FULL) OF 'CED_INFO_MOUVEMENT' (Cost=19 Card=24507 Bytes=416619) Statistics -- 0 recursive calls 116 db block gets 246 consistent gets 0 physical reads 0 redo size 206 bytes sent via SQL*Net to client 248 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed I tryed vith HINTS FULL and I got 5227 rows. Regards. NAME VALUE - - active_instance_count always_anti_join NESTED_LOOPS always_semi_join standard aq_tm_processes 0 audit_file_dest ?/rdbms/audit audit_trail NONE background_core_dump partial background_dump_dest /sscedre/data/sqcedi/admin/log backup_tape_io_slaves FALSE bitmap_merge_area_size1048576 blank_trimmingFALSE buffer_pool_keep buffer_pool_recycle commit_point_strength 1 compatible8.1.0 control_file_record_keep_time 7 control_files /sscedre/data/sqcedi/disk1/ctrl11sqcedi.ctl, /sscedre/data/sqcedi/disk2/ctrl12sqcedi.ctl, /sscedre/data/sqcedi/disk3/ctrl13sqcedi.ctl core_dump_dest?/dbs cpu_count 4 create_bitmap_area_size 8388608 cursor_sharingEXACT cursor_space_for_time FALSE db_block_buffers 3200 db_block_checking FALSE db_block_checksum FALSE db_block_lru_latches 2 db_block_max_dirty_target 3200 db_block_size 8192 db_domain db_file_direct_io_count 64 db_file_multiblock_read_count 32 db_file_name_convert db_files 500
Re: select count(*) = 0, select /*+ full(a) */ count(*) = 5227 ?
I had the same issue in a 8.1.6 database and found that was bug related to the cost based optimizer and materialized views. Never did resolve it but found that the problem occurred when the table in question had a materialized view selecting data from in. Thanks, caj - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 30, 2002 9:23 AM On Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production PL/SQL Release 8.1.7.2.0 - Production CORE8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I got a stange result and I wish to find an explanation. Look I did SQL select count(*) from ced_info_mouvement ; cls COUNT(*) -- 0 1 ligne sÚlectionnÚe. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 PARTITION HASH (ALL) 4 3 INDEX (FAST FULL SCAN) OF 'MVT_ID_LOT_IDX' (NON-UNIQUE) (Cost=2 Card=24507) Statistics -- 0 recursive calls 288 db block gets 120 consistent gets 0 physical reads 0 redo size 203 bytes sent via SQL*Net to client 248 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed I tryed with and order by on the first column_name, I got the exact number of rows in this table, why ? SQL select count(*) from ced_info_mouvement order by IDENTIF_PRODUIT_COMPTABLE; cls COUNT(*) -- 5227 1 ligne sÚlectionnÚe. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=17) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 PARTITION HASH (ALL) 4 3 TABLE ACCESS (FULL) OF 'CED_INFO_MOUVEMENT' (Cost=19 Card=24507 Bytes=416619) Statistics -- 0 recursive calls 116 db block gets 246 consistent gets 0 physical reads 0 redo size 206 bytes sent via SQL*Net to client 248 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed I tryed vith HINTS FULL and I got 5227 rows. Regards. NAME VALUE - - active_instance_count always_anti_join NESTED_LOOPS always_semi_join standard aq_tm_processes 0 audit_file_dest ?/rdbms/audit audit_trail NONE background_core_dump partial background_dump_dest /sscedre/data/sqcedi/admin/log backup_tape_io_slaves FALSE bitmap_merge_area_size1048576 blank_trimmingFALSE buffer_pool_keep buffer_pool_recycle commit_point_strength 1 compatible8.1.0 control_file_record_keep_time 7 control_files /sscedre/data/sqcedi/disk1/ctrl11sqcedi.ctl, /sscedre/data/sqcedi/disk2/ctrl12sqcedi.ctl, /sscedre/data/sqcedi/disk3/ctrl13sqcedi.ctl core_dump_dest?/dbs cpu_count 4 create_bitmap_area_size 8388608 cursor_sharingEXACT cursor_space_for_time FALSE db_block_buffers 3200 db_block_checking FALSE db_block_checksum FALSE db_block_lru_latches 2 db_block_max_dirty_target 3200 db_block_size 8192 db_domain db_file_direct_io_count 64 db_file_multiblock_read_count 32 db_file_name_convert db_files 500 dblink_encrypt_login FALSE db_name sqcedi dbwr_io_slaves0 db_writer_processes 1 disk_asynch_ioTRUE distributed_transactions 23 dml_locks 500
