Our DBA has been flaming me in emails because I didn’t give her a script to build a new index. I contend that it’s the DBA’s responsibility to know how to build the index, and more specifically, what the storage specification should be for that index. As a developer, I don’t have an easy way of determining what tablespaces (storage areas) can accommodate the index or cannot.
This particular altercation revolves around an index I wanted put on a 73 row table that has only one column. I think it’d be tantamount to an insult to tell the DBA how to build this.
Here at work, we have application DBA’s and system DBA’s. The application DBA’s manage the tables, and the system DBA’s manage the allocation of space and such. It’s really stupid how they have it segregated here, but hey, that’s how they do it. Maybe that’s your problem? If so, have a systems person look at it.
AFter some minor raging, she did it. But the issue for me was not how to do it, because it’s a total nobrainer. The problem is that I don’t have the tools to look at the tablespaces, nor the privileges to create an index on a production database.
Both developers and dbas do that at my work. I’m no help.
>>>The problem is that I don’t have the tools to look at the tablespaces, nor the privileges to create an index on a production database.
If the dba has these rights (can’t imagine they wouldn’t) while you don’t, then of course its his/her responsibility.
I hate to be the devil’s advocate but is a the performance difference between a full table scan compared to an index scan even significant? IN any event the DBA shoulod be involved for reasons mentioned above and to monitor query performance to make sure your index is actually being used as expected.
Storage, lol. I am so glad I moved from Oracle to Teradata. No more tablespaces, database files or reorganization. Allocating multiple terabytes of effectively contiguous space becomes trivial.