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 > 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 > 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_size 1048576 > blank_trimming FALSE > buffer_pool_keep > buffer_pool_recycle > commit_point_strength 1 > compatible 8.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_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 TRUE > ifile > 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 6000 > local_listener > lock_name_space > 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 > nls_timestamp_tz_format > nls_time_tz_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/log > utl_file_dir > /var/spool/applmgr/sqcedi/common/log > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Bernard, Gilbert > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). > ********************************************************************** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ********************************************************************** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord, David - CSG INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
