Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: UNION CASTING .

Re: UNION CASTING .

From: Maze Control - Terminal 23315 - Central 2 <trw-sinterface_at_pluto.gwy>
Date: Mon, 30 Apr 2001 11:27:12 +0100
Message-ID: <c4bH6.12725$_W2.12674@news.indigo.ie>

Thank you very much for the answer.
It has been the closest thing to a real answer yet. No disrespect to any of the other answers - they have been trying to address this from a front-end - patch the SQL rather than the DB.

In answer to the first point you make, I am a consultant and this is what they told me had already had been spent. If it seems like an exaggeration I am sorry. I have no come-back whatsoever, though.
I cannot prove it is necessary to do this I am only experiencing a problem and trying to resolve. PS the recommended workaround is to create a table on the views. As I said already, I do not have that many GB.
I will try your sample and see.

Thanks

Chris.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:988623137.5980.0.nnrp-14.9e984b29_at_news.demon.co.uk...
>
> With all due respect, if your project manager cannot
> be persuaded that there is no security risk in editing
> a trace file to make table and column names anonymous,
> and if your project team has not been able to figure
> out in 400 man-days (or should that be person-days)
> how to patch the data dictionary, then you cannot have
> the expertise on the project to prove that it is necessary to
> patch the data dictionary.
>
> However, the SYS.COL$ table has a column named
> NULL$, which, according to the sql.bsq script, is
> set to zero if nulls are allowed and a value greater
> than zero if nulls are not allowed. The following script
> took about 5 minutes to prepare, and may be sufficient -
> however I haven't thought it through in detail. If you do
> choose to use something like this, make sure that you
> implement it to update just one column, and then
> check that it has updated just one column.
>
> alter system flush shared_pool;
>
> update col$
> set null$ = 1
> where name = 'critical column name'
> and obj# = (
> select obj#
> from obj$
> where owner# =
> (
> select user#
> from user$
> where name = 'view owner'
> and type# = 1
> )
> and name = 'view name'
> )
> ;
>
> alter system flush shared pool;
>
>
> I have tested this script only on a new schema
> on Oracle 8.1.7.0 running under Windows NT 4.0
>
>
> By the way, I am aware that the CBO can select different
> paths depending on whether a critical column is declared
> as nullable or not, so I agree with you that Oracle's failure
> to propagate the constraint to the view could be labelled a
> defect. However, since there are no statistics on views
> anyway, it is possible that Oracle resolves this issue at
> parse-time by access to the table definitions.
>
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
>
>
> Maze Control - Terminal 23315 - Central 2 wrote in message
> <8y9H6.12710$_W2.12737_at_news.indigo.ie>...
> >Thanks for your comments.
> >
> >Unfortunately I do not have permission to post 10053 trace to this group,
> >although the gist of it is that it selects filters and so on instead of
> >using Hash Join.
> >I am not trying to correct the optimizer; I am trying to give it the
> >correct data to work with. This problem is not specific to me, and I
 have
> >noted it both on Dynix and Win2000 Ora 817.
> >
> >There is no intermediate result table.
> >
> >However, I have been through all this within the business group that I
 have
> >been working within, and posting here is very much last-resort and
 trying
> >to get the wider DBA community, to help. Unfortunately confidentiality
> >issues preclude me from posting much real data.
> >
> >Asking the original question once more,
> >'How does one patch the dictionary to reflect the fact correctly that
 there
> >are no nulls here'. For anyone else reading this please note the
 following.
> >
> >1: I am fully aware of what I am asking.
> >2: Support can be disregarded for the moment. Please understand this. We
> >will argue with Oracle ourselves.
> >3: A tremendous amount of man-hours have been spent and discarded already
 on
> >this issue, probably about 400MD, days, and our backs are to a wall.
> >
> >TIA
>
>
>
Received on Mon Apr 30 2001 - 05:27:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US