Oracle Indici - Cronologia

WikiSysop: /* indici nosegment (virtual indexes) */

2026-10-05T13:30:25Z

indici nosegment (virtual indexes)

← Versione meno recente		Versione delle 13:30, 5 ott 2026
Riga 45:		Riga 45:
	create index cust_idx1 on cust(first_name) nosegment;		create index cust_idx1 on cust(first_name) nosegment;

			Per fare in modo che l'optimizer valuti correttamente se usare o meno l'indice, si possono calcolare le statistiche:

			EXEC DBMS_STATS.gather_index_stats(ownname=>'USER', indname =>'CUST_IDX1');


	Per vedere se usa l'indice, anche se (di fatto) non esiste:		Per vedere se usa l'indice, anche se (di fatto) non esiste:

2026-10-05T13:26:23Z

indici nosegment (virtual indexes)

← Versione meno recente		Versione delle 13:26, 5 ott 2026
Riga 45:		Riga 45:
	create index cust_idx1 on cust(first_name) nosegment;		create index cust_idx1 on cust(first_name) nosegment;


⚫	alter session set "_use_nosegment_indexes"=true;


	Per vedere se usa l'indice, anche se (di fatto) non esiste:		Per vedere se usa l'indice, anche se (di fatto) non esiste:
	set autotrace traceonly explain;		set autotrace traceonly explain;
		⚫	alter session set "_use_nosegment_indexes"=true;
	select first_name from cust where first_name = 'BUBBA';		select first_name from cust where first_name = 'BUBBA';

			Se viene usato, si può creare un "vero" indice.
			Per trasformarlo in un indice "vero":
			create index cust_idx1 on cust(first_name);

	=indici compress=		=indici compress=

2026-10-05T13:23:40Z

indici nosegment

← Versione meno recente		Versione delle 13:23, 5 ott 2026
Riga 42:		Riga 42:


	=indici nosegment=		=indici nosegment (virtual indexes)=
	create index cust_idx1 on cust(first_name) nosegment;		create index cust_idx1 on cust(first_name) nosegment;

Riga 51:		Riga 51:
	set autotrace traceonly explain;		set autotrace traceonly explain;
	select first_name from cust where first_name = 'BUBBA';		select first_name from cust where first_name = 'BUBBA';


	=indici compress=		=indici compress=

2026-10-05T12:53:01Z

Elenco Indici

2026-09-11T15:18:33Z

← Versione meno recente		Versione delle 15:18, 11 set 2026
Riga 43:		Riga 43:

	=indici nosegment=		=indici nosegment=
			create index cust_idx1 on cust(first_name) nosegment;

			alter session set "_use_nosegment_indexes"=true;


			Per vedere se usa l'indice, anche se (di fatto) non esiste:
			set autotrace traceonly explain;
			select first_name from cust where first_name = 'BUBBA';


			=indici compress=


	[[Categoria:Database]]		[[Categoria:Database]]

2026-09-11T15:15:52Z

← Versione meno recente	Versione delle 15:15, 11 set 2026
Riga 1:	Riga 1:
		=Indici=
		==Elenco Indici==
		set pagesize 200
		set linesize 140
		col column_name for a30
		col pos for 99
		col table_name for a30
		col index_name for a35
		select table_owner \|\| '.' \|\| table_name table_name, index_owner \|\| '.' \|\| index_name index_name, column_name, column_position pos
		from dba_ind_columns
		where table_name=upper('&&1')
		order by 1,2,4;

		<pre>
		set pagesize 200
		set linesize 140
		set autotrace off
		col column_name for a30
		col pos for 99
		col table_name for a38
		col index_name for a43
		col tbs for a20
		select c.table_owner \|\| '.' \|\| c.table_name table_name, c.index_owner \|\| '.' \|\| c.index_name \|\| ' ' \|\| i.generated index_name,
		substr(i.uniqueness,1,1) U, c.column_name, c.column_position pos, i.tablespace_name tbs
		from dba_ind_columns c
		inner join dba_indexes i on
		(c.table_owner=i.table_owner and c.table_name=i.table_name and
		c.index_owner=i.owner and c.index_name=i.index_name)
		where c.table_name=upper('&&1')
		order by 1,2,4;

		col owner for a40
		col tablespace_name for a40
		select owner,tablespace_name,bytes/(1024*1024) mb from dba_segments where segment_name=upper('&&1');


		</pre>

		==Indici con proprietario diverso==
		select owner,index_name,table_owner,table_name from dba_indexes where table_owner!=owner;

2026-09-11T15:15:15Z

Creata pagina con " =indici nosegment= Categoria:Database Categoria:Oracle"

Nuova pagina

=indici nosegment=

[[Categoria:Database]]
[[Categoria:Oracle]]