Understanding Confidence in Teradata Explain Plans

I'm a big fan of Carrie Ballinger's Tech Support column in the Teradata Magazine. Carrie is a Teradata Certified Master and works in the Teradata Performance Centre in Los Angeles.

Her latest article came as a revelation to me because I've spent many years working on Teradata systems and I thought I new about Confidence until I read the article. The following is an edited summary, the full article is available here.

Confidence levels indicate the degree to which Statistics are (or are not) available for a given query step. Confidence is driven by statistics and the more complete the statistics, the higher the confidence level will be. Both high and low confidence are perfectly acceptable - both are treated the same for optimizer costing purposes.

Confidence takes the form of four discrete values

High Confidence

High confidence will appear on a step that performs database access if a single predicate exists in the query and statistics exist on the predicate AND the primary index.

Multiple columns in the predicate are allowed, as long as multi-column statistics cover them.

Low Confidence

You get Low Confidence if usable statistics are present (either from collected statistics or random sampling).

You'll also get low confidence if;

• Multiple predicates appear in the query for the table being accessed.
• No PI statistics are collected
• The step is a JOIN step

Index Join Confidence

Index Join Confidence will appear in instances when an index is present on one side of the join, but no index or statistics are on the other side.

No Confidence

No Confidence is assigned when no usable statistics exist for a step. No confidence trickles down to subsequent dependant steps, even if they have usable statistics. Subsequent steps may therefore display as No Confidence when in fact they are making use of available statistics.


Thanks to Carrie for shedding light on this.

Chilled Distribution Centres

I was lucky enough today to be taken on a tour of a chilled and frozen distribution centre. I've worked in retail for more than 15 years and been on a few DC tours in my time, but I was particularly excited at the prospect of this tour, as it was my first trip round a chilled DC.

Depots really are amazing places. I was particularly pleased to see pick by line in operation, since traditional warehousing usually involves pick by store, which I've seen a number of times. Pick by line works by taking a cage of a single product, and delivering it to the cages of a large number of stores. This type of picking is used in DC's that don't have any racking and where the goods that come in, go out the same day.

The technology has moved on a bit since my last trip too. Of course the picking is paperless, I expected that, but gone are the arm-mounted terminals, it's all done via voice activated headsets.

Voice picking, reponds to the instructions given by the picker and delivers the instructions to him/her. Working down a line of store cages, the picker speaks the name/number of the store and is told the number of units to pick for that store. It attaches quite nicely the hard hat baseball cap which is another nice touch.

This particular depot also had a large frozen section. Operating at about -25C, warm clothing is a must. The guys who work in here 8 hours per day are all clad in salopettes, big jackets, hats, scarves, gloves etc. This section is like a traditional ambient depot except for the temperature. Standard procedures of goods in, put away, letdown and pick by store take place.

A fascinating experience...

Queuing at Disneyland Paris

I'm fascinated by queuing and how organisations can manage them better, or avoid them completely. I've just got back from a weekend at Disneyland Paris, an experience that gave me plenty of food for thought on the queuing front.

I assume that Disneyland Paris is the busiest amusement park in Europe (I haven't checked) and therefore there isn't huge scope to avoid queues in this scenario. It's very much about how best to manage them, and Disney have thought long and hard about this and come up with a number of tricks.

Queue Time – All rides have a queue time sign at the start and for the most part this was very accurate. This allows you to make an informed decision before you join the queue on how long you’re going to be in it. In a subtle way, this shifts the responsibility onto yourself. You can’t complain about queuing for an hour, if you knew in advance that it was going to take that long.

The long and winding queue – For the most part the queues are unnecessarily windy. This means that you can’t normally see the route of the queue. Even if it’s in one big block, which means you are never quite sure how long the queue is. Even if you know it’s a long queue, it somehow doesn’t feel as bad when you can’t see it all laid out in front of you.

The double queue – This one’s a bit sneaky and gives rise to a few grumbles. You queue outdoors for a period of time and then when you get to the doors and go inside, thinking you’re at the front, you find another queue inside just as long and you realise your only half way. Personally I hate this. They use this in NHS hospitals quite a lot too. You turn up for a 2:30pm appointment and take a seat in the waiting room. At 2:30 they call your name and you start to think ‘Hey this is good’. They then take you round the corner to a smaller waiting room, (with more people in it than the larger one you just left, work that one out ) where you spend the next hour or two.

Making the queue part of the ride – This is a good distraction technique which ranges all the way from the scenery in the queuing environment, through video’s, sound animatronics etc all the way up to the pre-ride briefing.

Pre-ride Briefing – The designers might explain that the pre-ride briefing is about creating mood, telling a story, explaining vital safety information. Don’t be fooled. The pre-ride briefing is almost certainly followed by another queue. They’ve distracted you then hit you with the double queue strategy and you probably haven’t even noticed.

Virtual Queuing – Also called FastPass (Qbot at Legoland), is the latest development on the queuing front. You take a ticket that allows you to turn up at a ride and walk straight to the front of the queue. You’re usually only allowed one of these at a time. This allows you to queue for the ride without actually queuing for it (virtual queuing in fact), which isn’t really queuing at all in my book. This is a great idea in principle. In practice it’s a bit complicated which means there are winners and losers, those who can understand and milk the system and those who can’t. In one of my previous posts on queuing I’ve highlighted before that queues cost money. Fastpass requires two queues and the management of them and so takes up twice as much money (in simple terms). Plus it also generates bad feeling in those who have waited those glorious 90 minutes in the queue. No matter how well you understand and accept the system, I defy anyone to be happy about someone walking to the front and taking your space. You can’t get rid of the feeling that you’ve been cheated, a feeling that lingers because of the generally flimsy nature of the fastpass queue management. It’s easy to slip into Fastpass without a ticket, either at the start when no-one is watching, or later when you can usually just hop over a rail.