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
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
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).
