Toad World® Forums

Virtual (nosegment) indices.


#1

In Toad 10.0 GA, on the create index dialogue tabs, is there anywhere I
can put ‘nosegment’ to create a virtual index?

I can’t see one anywhere so I just send the SQL to the editor and added
it manually.

Thanks,
Norm. [TeamT]

Norman Dunbar
Contract Oracle DBA
CIS Engineering Services
Internal : 7 28 2051
External : 0113 231 2051

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#2

Hi Norm –

No, there is nothing in Toad to support them at this time. I did a quick look in
the documentation for ‘create index’ and didn’t find
‘nosegement’ anywere. Finally, I googled and found that this
apparently is an undocumented feature. After reading some of their
characteristics (not showing up in data dictionary views, for example) I think
we’d be better off supporting this only in the editor. Of course, if there
is great opposition to my opinion, we can add it anyway.

-John


#3

Hi John,

No, there is nothing in Toad to support them at this time.
Ok, I’m not blind then! :wink:

I did a quick look in the documentation for ‘create index’
and didn’t find ‘nosegement’ anywere.
I’ve just checked through the docs for 8i, 9i, 10g and 11g - as you have
found, not a sausage!

As for the nosegment clause on create index, it’s been around since at
least 8i - I’ve just created one in an 8174 database with no problems.

Finally, I googled
and found that this apparently is an undocumented feature.
Looks that way.

After reading some of their characteristics (not showing up
in data dictionary views, for example) I think we’d be
better off supporting this only in the editor.
Ok, I can live with that. No worries.

Of course, if there is great opposition to my opinion, we can add it
anyway.
The other slight problem is the need to alter session set
“_use_nosegment_indexes” = true before the CBO will tell you if they are
any good or not. And that’s an “underscore” parameter …

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#4

Virtual Columns & Indexes blog:

http://www.toadworld.com/BLOGS/tabid/67/EntryID/111/Default.aspx


#5

Hi Bert,

Virtual Columns & Indexes blog:
http://www.toadworld.com/BLOGS/tabid/67/EntryID/111/Default.aspx

I read it this morning! :wink:
But thanks.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#6

not showing up in data dictionary views, for example

Just from the perspective of a cautious developer (I tend not to use new
features till they’re in at least their second database version),
I’d say I’d avoid that feature from just that one … err
… property.

Call it a personal quirk, but when I query up the data dictionary views, I like
to see the objects that I’ve created.

RAS


#7

Morning Roger,

Just from the perspective of a cautious developer (I tend
not to use new features till they’re in at least their
second database version), I’d say I’d avoid that feature
from just that one … err … property.
They have been around since 8i.

Call it a personal quirk, but when I query up the data
dictionary views, I like to see the objects that I’ve created.
In 8i, they show up in DBA_OBJECTS and DBA_INDEXES. After 8i, they only
show up in DBA_OBJECTS.

CREATE TABLE norm(a number, b number);

CREATE INDEX norm_a ON norm(a) NOSEGMENT;

– One row selected.
SELECT * FROM dba_objects WHERE object_name = ‘NORM_A’;

– Nothing selected. Except in 8i where one row is selected.
SELECT * FROM dba_indexes WHERE index_name = ‘NORM_A’;

– Nothing selected.
SELECT * FROM dba_segments WHERE segment_name = ‘NORM_A’;

– Nothing selected.
SELECT * FROM dba_extents WHERE segment_name = ‘NORM_A’;

DROP TABLE norm;

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#8

Norm!

Just from the perspective of a cautious developer (I tend
not to use new features till they’re in at least their
second database version), I’d say I’d avoid that feature
from just that one … err … property.

They have been around since 8i.

Something sticks in my head that OEM uses them for statement tuning, but
don’t quote me on that.

I should be doing my portion of my annual review now…

Rich – [TeamT]

Disclaimer: Free speech? Your phone bill is a lot lower than mine.


#9

Evening Rich,

Something sticks in my head that OEM uses them for statement tuning, but
don’t quote me on that.
That’s about right. It’s either OEM or the SQL Tuning Advisor thingy
built in to OEM (?? I’m not sure, I use Toad!)

Still undocumented at 11gR2 as far as I can see. Maybe they don’t work -
plus - using them depends on an undocumented underscore parameter
_use_nosegment_indexes - so until that loses the underscore, they will
remain undocumented.

Like autonomous transactions were for years!

Cheers,
Norm. [TeamT]