| 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 ?
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
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)
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
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)
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 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: Naveen Nahata INET: naveen_nahata_at_mindtree.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:03:28 CDT
 |
 |