| 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 ?
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:Gilbert.Bernard_at_caissedesdepots.fr]
> 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: 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).
>
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: David.Lord_at_hayscsg.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 - 09:18:29 CDT
 |
 |