| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
On
Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production
PL/SQL Release 8.1.7.2.0 - Production
CORE 8.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
2 1
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)
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
2 1
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)
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_size 1048576 blank_trimming FALSE buffer_pool_keep buffer_pool_recycle commit_point_strength 1 compatible 8.1.0 control_file_record_keep_time 7control_files
/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_sharing EXACT 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_slaves 0 db_writer_processes 1 disk_asynch_io TRUE 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 fixed_date gc_defer_time 10 gc_files_to_locks gc_releasable_locks 0 gc_rollback_locks 0-1024=32!8REACH global_names FALSE hash_area_size 1600000 hash_join_enabled TRUE hash_multiblock_io_count 0 hi_shared_memory_address 0 hpux_sched_noage hs_autoregister TRUEifile
instance_groups instance_name sqcedi instance_number 0 java_max_sessionspace_size 0 java_pool_size 20000K java_soft_sessionspace_limit 0 job_queue_interval 60 job_queue_processes 0 large_pool_size 0 license_max_sessions 0 license_max_users 0 license_sessions_warning 0 lm_locks 12000 lm_ress 6000local_listener
lock_sga FALSE log_archive_dest log_archive_dest_state_1 enable log_archive_dest_state_2 enable log_archive_dest_state_3 enable log_archive_dest_state_4 enable log_archive_dest_state_5 enable log_archive_dest_1 log_archive_dest_2 log_archive_dest_3 log_archive_dest_4 log_archive_dest_5 log_archive_duplex_dest log_archive_format %t_%s.dbf log_archive_max_processes 1 log_archive_min_succeed_dest 1 log_archive_start FALSE log_archive_trace 0 log_buffer 5242880 log_checkpoint_interval 10000 log_checkpoints_to_alert FALSE log_checkpoint_timeout 1800 log_file_name_convert max_commit_propagation_delay 700 max_dump_file_size 10240 max_enabled_roles 30 max_rollback_segments 30 mts_circuits 0 mts_dispatchers mts_listener_address mts_max_dispatchers 5 mts_max_servers 20 mts_multiple_listeners FALSE mts_servers 0 mts_service sqcedi mts_sessions 0 nls_calendar nls_comp nls_currency nls_date_format DD-MON-RR nls_date_language nls_dual_currency nls_iso_currency nls_language American nls_numeric_characters ., nls_sort BINARY nls_territory America nls_time_format nls_timestamp_format
object_cache_max_size_percent 10 object_cache_optimal_size 102400 open_cursors 400 open_links 4 open_links_per_instance 4 ops_interconnects optimizer_features_enable 8.1.7 optimizer_index_caching 0 optimizer_index_cost_adj 100 optimizer_max_permutations 80000 optimizer_mode CHOOSE optimizer_percent_parallel 0 oracle_trace_collection_name oracle_trace_collection_path ?/otrace/admin/cdf oracle_trace_collection_size 5242880 oracle_trace_enable FALSE oracle_trace_facility_name oracled oracle_trace_facility_path ?/otrace/admin/fdf os_authent_prefix ops$ os_roles FALSE O7_DICTIONARY_ACCESSIBILITY TRUE parallel_adaptive_multi_user FALSE parallel_automatic_tuning FALSE parallel_broadcast_enabled FALSE parallel_execution_message_size 2152 parallel_instance_group parallel_max_servers 5 parallel_min_percent 0 parallel_min_servers 0 parallel_server FALSE parallel_server_instances 1 parallel_threads_per_cpu 2 partition_view_enabled FALSE plsql_v2_compatibility FALSE pre_page_sga FALSE processes 75 query_rewrite_enabled FALSE query_rewrite_integrity enforced rdbms_server_dn read_only_open_delayed FALSE recovery_parallelism 0 remote_dependencies_mode TIMESTAMP remote_login_passwordfile NONE remote_os_authent FALSE remote_os_roles FALSE replication_dependency_tracking TRUE resource_limit FALSE resource_manager_plan rollback_segments r01, r02, r03, r04 row_locking ALWAYS serializable FALSE serial_reuse DISABLE service_names sqcedi session_cached_cursors 0 session_max_open_files 10 sessions 87 shadow_core_dump partial shared_memory_address 0 shared_pool_reserved_size 1600000 shared_pool_size 32000000 sort_area_retained_size 800000 sort_area_size 800000 sort_multiblock_read_count 2 sql_trace FALSE sql_version NATIVE sql92_security FALSE standby_archive_dest ?/dbs/arch star_transformation_enabled FALSE tape_asynch_io TRUE text_enable FALSE thread 0 timed_os_statistics 0 timed_statistics FALSE tracefile_identifier transaction_auditing TRUE transactions 95 transactions_per_rollback_segment 5 use_indirect_data_buffers FALSE user_dump_dest /sscedre/data/sqcedi/admin/logutl_file_dir
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert INET: Gilbert.Bernard_at_caissedesdepots.fr Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Casey A. Jordan INET: cjordan_at_imcwv.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Aug 30 2002 - 10:58:25 CDT
 |
 |