Re: Improve this querry!?

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1068483998.189348_at_yasure>...
> Then I don't understand what 73 minutes of records is, or are. Can you
> rephrase this?

I'll try my best Mr. Morgan. Let me apologize in advance for my abuse of the English language and my typographical errors.

In the original query, I commented the lines your question refers too.  ( I think that could be proper use of the word "too".?)

med.inputserialnumber > HEXTORAW('3FA6CDF541000013') AND -- Mon Nov 3 21:51:49 2003
med.inputserialnumber < HEXTORAW('3FA6DEF541000013') AND -- Mon Nov 3 23:04:21 2003

I didn't think this would be a point of such contention. The only reasonable conclusion for the inputserialnumber to be delimited with the less than and greater than operators would be that there is a limitation of the inputserialnumber. Since the comments on each line clearly specify a specific time (in fact they are approximately 73 minutes apart), one could assume the value of the inputserialnumber is somehow related to time. The fact is the RAW value for inputserialnumber is a HEX (hexadecimal) number that translates to time (in seconds)/number. To further clarify, the first half of the HEX number is the time in seconds since 1 JAN, 1970. The second half of the HEX number is a unique number for each message received in that particular second in time (that could be - "of time" - I'm not sure).

> >>Have you run Explain Plan?
> >>
> >>
> >
> >No. I wouldn't readily understand the output anyway. And when I say
> >that, I mean I COULD do it, but I figure I'd spend more time trying to
> >understand it than it was worth.
> >
> Running Explain Plan is essential. The fact that indexes exist does not
> mean that indexes are used.
> Go to http://tahiti.oracle.com and print out the documentation for
> running Explain Plan. Basically
> you will need to run the utlxplan.sql script in $ORACLE_HOME/rdbms/admin
> to build the plan
> table and then use the explain plan syntax to create the plan. Printing
> it out can be done with any
> number of scripts available all over the web. Contact me off-line if you
> can't find a copy.
>
> You may not understand everything about the output ... but you will be
> able to tell immediately
> whether the indexes are being used.

Again, I'd be happy to run the Explain Plan if it will somehow lead to a solution. I can't help thinking it will just bring further questions and more actions.

>
> >>What is the cardinality?
> >
> >Big (to me) - I'm sure by your question you have seen larger
> >databases. We get about 13 million messages a day. The database is
> >48g. We get about 10000 messages per minute (input) and the ratio is
> >1.8 avg output to each input message. The med_mt_tb is the input
> >message (1 unique record [ISN]) and the mrd_mt_tb is the output
> >messages (1 record for each delivery [same ISN as input]) . The
> >database has 5-7 days worth of data.
> >
> "Big (to me) ... They've really go to start teaching English in
> schools. ;-)
>
> The question is worthy of your understanding if you are going to take
> your employers money
> for database work. Look this one up in a book or at
> http://tahiti.oracle.com.

No need to "Look this one up in a book or ..." - I perfectly understand the question as it is written. If you MEANT something different, I apologize for not inferring that meaning. I thought you meant the "cardinality" or "size" of the the two tables - silly me! I thought I could explain that to you by telling you that the med had unique (or distinctive) values for inputserialnumbers and the ratio of records between the two tables. If that doesn't answer your question, can you please rephrase this?

> >>What percentage of records are being returns?
> >>
> >>
> >
> >A 73 minute period would be less that 1 percent. (using 6 days of
> >data). I would have to run this more that 100 times
> >
> Are you trying to say that if you request the data that was input over a
> 73 minute period that
> corresponds with requesting 1% of the records in the table?

Yes - again, I apologize for the typos.
> I don't mean this in a prejorative way but it seems you are clearly not
> the person to solve this problem.

Seems we all make mistakes from time to time. Rather than say I don't understand this statement, I'll just assume you meant "pejorative". If you worked in the section I do and with the people I do, you may form a different opinion after a short time.

> I'd suggest that your firm hire a consultant to help you
> through this and make
> one condition of that person's employment that they provide you with
> training.

Thanks anyway for your time. Other people on other boards have been very helpful in finding a solution to my question. You however have been very helpful in pointing out my deficiencies in English and my lack of understanding Oracle - which I already knew! I thought the reason these boards were set up in the first place was for people to get help with their questions.
> I'd also strongly suggest that unless you want to make a career change
> you put great
> effort into securing an education on everything from database
> fundamentals to Oracle
> concepts and architecture to SQL. This is not a good time, not that
> there ever is one,
> to be so poorly equipped for the job you are doing. Unemployment lines
> are full of
> people whose skills are marginal.

I'll work on that. Maybe I should further consider retiring from the military, since I have now completed 20+ years of service and attained the rank of MSgt, to pursue your suggestion. But regardless or your non-pejorative intentions, I feel strongly to continue my service protecting others and giving THEM the freedom to attain all the knowledge they need in hopes that they will continue to help others on this board with problems they may have. Some of us choose other options than JUST education.

Thanks again for your time.

God Bless,
Mark. Received on Tue Nov 11 2003 - 11:06:58 CST